Clever Geek Handbook

Oracle Outer Joins - Performance

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

There are times when one or the other join syntax might work better. I also found a time when I noticed a slight performance increase (only noticeable in VLDB) when choosing the Oracle join syntax over ANSI. This is probably not enough to be confusing, but for anyone serious about mastering Oracle DB it might be helpful to browse the article.

I am aware of two external 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 from the right.)

Is there a performance difference between the two? At first I thought it should be a style preference on the part of the developer, but then I read something that made me think there might be a reason to use one style over the other.

+2


source to share


6 answers


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



There are reasons, but not related to performance. Outer joins of ANSI type, as well as standard ones, offer FULL OUTER JOINs and outer joins for multiple tables.

+4


source


Oracle

did not support ANSI

pre-release syntax 9i

.

From this version on, these queries do the same and give the same plan.



The correct pre- 9i

syntax is:

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


source


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

+1


source


In theory, the second query performs the Cartesian product of the two tables and then selects those that match the join condition. In practice, however, the database engine will optimize it in exactly the same way as the first.

+1


source


I found more information in response to my own question. It looks like the old style is very limited, starting with this document 3 years ago.

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

I think it might be wise to use the old style if, for some reason, queries can run on an older version of Oracle.

Everything I see at work is pretty much everything old style, but probably only because the consultants worked at Oracle from 9i to 9i and they probably didn't see a reason to update all the old stuff.

Thanks everyone!

0


source


It is not the same. In the first case, you are forcing the join tables in that order. In the second case, Oracle Planner can choose the best option to fulfill the query.

In this trivial case, the result is likely to be the same in all implementations, but if you use this syntax in more complex cases, the difference will be shown.

0


source






More articles:


All Articles