RE: should one use ANSI join syntax when writing an Oracle application?

I find the ansi syntax verbose and agree with the poster who said that while it highlights the joins it obfuscates the tables which I find more important while debugging data issues. I don't understand why we need the ansi syntax to separate the join and filter conditions, I have been doing it for years in the where clause, first the joins then the filter, look at any of my queries and it is plain as day even with 20 tables 6 inline views and 4 sub-queries.

I also find the ansi syntax to imply a join order that the optimizer may not choose and I find complex outer join to very difficult to write with the ansi syntax so I only use the ansi syntax when I need functionality that has not been implemented with the (+) syntax. It does have several benefits, simple notation for full outer joins, collapsing of join columns when doing a select * when using the USING clause.

As for analytical functions, I adore them. They have made my life a lot easier since they were introduced in 8.1.6. I have taken queries that were 6 or 7 pages long full of self joins than ran for hours and converted them with analytical functions to 1 page queries that run in minutes. I have replaced a procedure with complex linking logic that took an hour to run to one query that runs in 28 seconds etc. In the data warehouse that I recently built without the use analytical functions I would have been lost; atleast half the queries in the entirely pl/sql etl code use analytic functions and 100% of the preprocessing (aka bad data cleanup uses it). I have come so dependent on them I will not work on a release prior to 8.1.6; luckily not that many of those are around anymore.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, October 17, 2006 8:44 AM
To: Bernard.Polarski_at_atosorigin.com; oracle-l_at_freelists.org Subject: Re: should one use ANSI join syntax when writing an Oracle application?

I find that it helps to think of the analytic functions as a presentation layer added into the SQL.

First write the SQL to acquire the data and aggregate it to produce the correct result set.

Then add analytic columns which add value to the data. Then optionally use some analytic results to reduce the output again.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "Polarski, Bernard" <Bernard.Polarski_at_atosorigin.com>

Having said that, the last tendency of analytic function violate 100% the philosophy of SQL
which was keep the syntax simple and leave difficulty on the action performed. Now if I don't practise the rollup, I tend to forget the syntax.

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 17 2006 - 09:10:45 CDT