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.