Re: What hint would you try?

From: Adric Norris <>
Date: Fri, 10 Jun 2011 10:49:23 -0500
Message-ID: <>

On Fri, Jun 10, 2011 at 08:41, Wolfgang Breitling <>wrote:

> I should have added - neither do I.
> When a developer comes to me for help tuning a sql with ANSI join syntax
> and I can't see the solution within about 5 minutes I tell them to come back
> with the sql rewritten in Oracle join syntax because neither I nor the
> optimizer speak ANSI.

Personally, I prefer the ANSI syntax... largely because it provides a clear delineation between the join conditions, and the potentially hundreds of miscellaneous filters which are present for some unknown (to me) reason. I also find the outer join syntax easier to parse, compared to deciphering a headache-inducing proliferation of "(+)" scattered throughout the WHERE clause.

Over the past year or two I've often rewritten queries to use ANSI syntax during troubleshooting, in an effort to better understand what they're actually doing. Now this doesn't mean that the final version is always in ANSI form, but I've found making the effort to be very helpful nonetheless. As usual, your mileage may vary.

In my experience, at least, 11gR2 seems to understand and handle the ANSI syntax quite well. I've only used it sparingly on earlier versions, so no comment there.

Received on Fri Jun 10 2011 - 10:49:23 CDT

Original text of this message