RE: should one use ANSI join syntax when writing an Oracle applic ation?
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'breitliw@xxxxxxxxxxxxx'" <breitliw@xxxxxxxxxxxxx>, Jacques.Kilchoer@xxxxxxxxx
- Date: Thu, 19 Oct 2006 14:58:06 +0100
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@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Wednesday, October 18, 2006 3:55 PM
To: Jacques.Kilchoer@xxxxxxxxx
Cc: oracle-l
Subject: RE: should one use ANSI join syntax when writing an Oracle
application?
Quoting Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx>:
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
--
//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
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
