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:
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.
yes. I like traditional (+) outjion syntax also.
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.
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.
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.
Sorry: in my case the db is 10.2.0.4.0 also, not as I stated.
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.
Post a Comment