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.

7 comments:

DomBrooks said...

It would be interesting if you had a complete standalone test case to demonstrate this behaviour.

My initial reaction would be to suggest moving the IS NOT NULL out of the JOIN ON condition and into a WHERE clause and see if that made a difference.

Anonymous said...

yes. I like traditional (+) outjion syntax also.

Anonymous said...

Did you ever figure out what was wrong? We are seeing the same issue consistently after upgrading from 10.2.0.2 to 10.2.0.4 oct 2009 Psu.

Krishna Manoharan said...

Never really spent a lot of time on debugging the problem. Since it was in development phase, we changed to Oracle syntax and fixed the issue.

Roger Pearse said...

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.

Roger Pearse said...

Sorry: in my case the db is 10.2.0.4.0 also, not as I stated.

Anonymous said...

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.