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.
Tuesday, February 2, 2010
Subscribe to:
Posts (Atom)