Oracle outer joins - performance

Question

EDIT 9-3-10: I found this blog entry recently that was very enlightening. http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html

There are times when one or the other join syntax may in fact perform better. I have also found times when a have noticed a slight performance increase (only noticeable in VLDBs) when choosing the Oracle join syntax over the ANSI one. Probably not enough to get fussy over, but for those serious about mastering the Oracle DB, it may be helpful to review the article.

---

I am aware of two outer join syntaxes for Oracle:

select a, b
from table1 
left outer join table2
on table2.foo = table1.foo

OR

select a, b
from table1, table2
where table2.foo(+) = table1.foo

(assuming I got the syntax of the second sample right.)

Is there a performance difference between these? At first I thought it must just be a style preference on the part of the developer, but then I read something that made me think maybe there would be a reason to use one style instead of the other.

Answer 1

There is no performance difference. You can also check the execution plans of both queries to compare.

Answer 2

Theoretically, the second query performs the Cartesian product of the two tables and then selects those meeting the join condition. In practice, though, the database engine will optimize it exactly the same as the first.

Answer 3

Oracle didn't support ANSI syntax prior to version 9i.

Since that version, these queries do the same and yield the same plan.

Correct pre-9i syntax is this:

SELECT  a, b
FROM    table1, table2
WHERE   table2.foo(+) = table1.foo

Answer 4

"maybe there would be a reason to use one style instead of the other. "

There are reasons, but not performance related ones. The ANSI style outer joins, as well as being standard, offer FULL OUTER JOINs and outer joins to multiple tables.

Answer 5

I found some additional information in answer to my own question. Looks like the old style is very limiting, as of this doc from 3 years ago.

http://www.freelists.org/post/oracle-l/should-one-use-ANSI-join-syntax-when-writing-an-Oracle-application,2

I think perhaps it would only make sense to use the old style if for some reason the queries might be run on an outdated version of Oracle.

The stuff I see at work is almost all in the old style, but it's probably just because the consultants have been working in Oracle since before 9i and they likely didn't see a reason to go update all the old stuff.

Thanks all!

Answer 6

Hi,

It's not the same. In the first case you're forcing to join the tables in that order. In the second case Oracle Planner can choose the best option to execute the query.

In this trivial case the result probably will be the same in all the executions, but if you use that sintax in more complex cases the difference will be shown.