Re: What hint would you try?

From: <japplewhite_at_austinisd.org>
Date: Fri, 10 Jun 2011 14:58:57 -0500
Message-ID: <OF01E7A0B1.70349D4D-ON862578AB.006CD184-862578AB.006DC1D7_at_austinisd.org>

+2. Our new Student Information System uses ANSI syntax almost
exclusively, so I have had to "grin and bear it" for almost a year (so I thought), having used Oracle "Classic" SQL for almost 3 decades.

Over the last 3 months I've had to do a complex ETL project from our current and previous SISes to a new 3rd Party App - kind of a Warehouse. I started out using the SQL Classic flavor, but soon tried the New and Improved ANSI flavor and now I really like it. For complex, multi-join-plus-outer-join SQL it's far easier to write, read, and debug. I haven't gotten erroneous results and performance seems fine - DBs are 10.2.0.4 on Linux.

I'm an old dog that is happy to have learned my new trick.

Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9250 (wk) / 512.935.5929 (pager)

From: Stephane Faroult <sfaroult_at_roughsea.com>

To:     landstander668_at_gmail.com
Cc:     oracle-l Freelists <oracle-l_at_freelists.org>
Date:   06/10/2011 11:20 AM
Subject:        Re: What hint would you try?
Sent by:        oracle-l-bounce_at_freelists.org

+1. Took me some time to get accustomed to the ANSI syntax, but I find it
more legible too. And I have never been able to directly link a performance issue to a use of the ANSI syntax; on the other hand, I have never tried *just* changing the syntax of joins and my own understanding of tuning is rather heavy-handed.

SF

On 06/10/2011 05:49 PM, Adric Norris wrote: On Fri, Jun 10, 2011 at 08:41, Wolfgang Breitling <breitliw_at_centrexcc.com> 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 10 2011 - 14:58:57 CDT