Tuesday, February 2, 2010

Joins - ANSI SQL versus Traditional Oracle (+) syntax

Sometime back (maybe with 9i?), Oracle introduced support for the ANSI join syntax - inner join, full outer join, left outer join and right outer join. This could be used in place of the (+) operator.

I came across a curious problem on Oracle 10.2.0.4. The explain plans were markedly different (with different execution times as well) when the sql was written using ANSI syntax versus traditional oracle (+) syntax. The traditional syntax was a lot more faster and even with passing hints with the ANSI style, I could not force a similar execution plan.

ANSI Style

Traditional Oracle Syntax

As to why the CBO was behaving such, I have no idea. But if you do run into problems with joins, it may make sense to change the sql to traditional oracle syntax.

Thank you so much for this post. I have just come across a situation with a complex query where using the ANSI outer join causes a horrible execution plan (but same results). But I can get it to run in a fraction of the time and with a far better explain plan simply by using the old native syntax!

I'm in 10.2.0.1 as well; there must be a bug.

But I haven't managed to reduce the query to something small enough to report.

Hi, the problem is in wrong transcription of one of the condition in traditional syntax - IS NOT NULL condition is not a join condition, so i think if you move it to the WHERE clause, you get different plan for the ANSI select syntax.