Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ANSI syntax slower than old style?

Re: ANSI syntax slower than old style?

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 23 Aug 2006 22:15:07 +0100
Message-ID: <qogpe25c9sv286dl9m4u348ttavn7bqt35@4ax.com>


On 23 Aug 2006 13:17:57 -0700, duncanstreet2004_at_yahoo.com wrote:

>Are there times when the new, ANSI syntax for joins is slower than the
>old style or can I assume that ANSI joins are just as fast if done
>properly?
>
>ANSI Joins
>SELECT * from DEPT D
>INNER JOIN EMPLOYEES E
>ON (D.DEPT_ID = E.DEPT_ID)
>WHERE D.DEPT_NAME = 'Human Resources'
>
>Old Style Joins
>SELECT * FROM DEPT D, EMPLOYEES E
>WHERE D.DEPT_ID = E.DEPT_ID
>AND D.DEPT_NAME = 'Human Resources'

 Look at the execution plan produced. If they're identical - which they should be if you are actually writing an equivalent statement - then there will be no difference in how it's executed. So, yes, they're just as fast if done properly.

 Earlier versions of Oracle had bugs in the parts of the parser dealing with this syntax, however, I've not encountered any of these in some time now.

 So it then mostly comes down to a subjective opinion on which to pick.

 IMHO the ANSI format is much clearer, not least because it separates join predicates from filter predicates.

 There are also some statements that you can write using the ANSI (LEFT|RIGHT) OUTER JOIN and FULL OUTER JOIN that you can't with the Oracle (+) operator - at least not without some ugly rearrangements, such as repeated subqueries and unions.

 The ANSI syntax is also more standard, so is more likely to be understood than the proprietary (+) operator.

 And finally, the Word of Oracle itself is:  (SQL Reference manual, from the chapter on Joins)

"Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:"

 [... followed by a load of restrictions ...]

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Received on Wed Aug 23 2006 - 16:15:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US