| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: should one use ANSI join syntax when writing an Oracle applic ation?
Wolfgang,
I wasn't sure what you meant by your point about full transitive closure in your earlier post, but your example makes it clear. Just to confirm, your point is that adding the (logically unnecessary) "and A.x = C.x" provides more information that the optimizer can use to choose a more efficient access path?
Thanks,
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
Sent: Wednesday, October 18, 2006 3:55 PM
To: Jacques.Kilchoer_at_quest.com
Cc: oracle-l
Subject: RE: should one use ANSI join syntax when writing an Oracle
application?
Quoting Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>: I need some help here. How do I code a full transitive closure join with the new syntax? In the traditional syntax I can say:
select A.x, B.y, C.z
from A, B, C
where A.x = B.x and B.x = C.x and A.x = C.x
How do I do that with the newfangled syntax. When I try
select A.x, B.y, C.z
from A
inner join B on ( A.x = B.x ) inner join C on ( B.x = C.x ) inner join C on ( A.x = C.x )
I get
select A.x, B.y, C.z
*
ERROR at line 1:
ORA-00918: column ambiguously defined
> And to answer this question: converting from the traditional syntax to newer
> ANSI syntax is easy, use the inner join and on keywords.
>
> select ... from A, B where A.x < B.y
> becomes
> select ... from A inner join B on ( A.x < B.y )
>
> select ... from A, B where A.x between B.y and B.z
> becomes
> select ... from A inner join B on ( A.x between B.y and B.z )
>
-- regards Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l ============================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ============================================================================== -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 19 2006 - 08:58:06 CDT
 |
 |