Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

Question

Way back when Michael Jackson was still alive, I made this question:

https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause

And now I'm performance-auditing a system in which the developer used the first form, instead of an INNER JOIN. The DBMS is Oracle 10.2.0.4 on Solaris/SPARC. I could, of course, recommend the change in syntax solely because it's better form, but if it won't positively impact performance it's kind of pointless.

So, my question is: is it possible there will be a change in query execution if I ask them to change the query syntax to INNER JOIN?

Answer 1

Is it possible? Sure. Is there likely to be an improvement in performance? No. If there is a change in performance (barring cases where you discover that a join is missing or otherwise fix a query), it'm more likely that the old implicit join syntax will be more efficient. But that's pretty unlikely.

Behind the scenes, when you have a query using the SQL 99 inner join\'left outer join` syntax, Oracle actually transforms that to use the old style syntax before optimizing it. When Oracle first introduced the SQL 99 syntax, there were occasionally bugs in this transformation process. By the time you get to 10.2.0.4, the odds that you'd encounter one of those bugs is pretty low. But if you look at enough queries, you might find one that is more efficient using the old syntax.