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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 18 Oct 2006 19:04:31 -0400
Message-ID: <003d01c6f309$c644f120$0c00a8c0@Thing1>

Jacques: I believe Wolfgang is just trying to give the CBO the extra information so that it has the option to estimate the cost of all the possible join orders. Without including the transitive equality of the join column you would get the same result set, but quite possibly with much more work, because if you leave one of them out some of the join orders are no longer valid.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jacques Kilchoer
Sent: Wednesday, October 18, 2006 4:16 PM To: Wolfgang Breitling
Cc: oracle-l
Subject: RE: should one use ANSI join syntax when writing an Oracle application?

Considering your reputation on SQL tuning issues, I am not going to ask WHY you would want to do such a thing :)

Maybe this is not the right way to look at it, but when I write statements with the new syntax, I interpret it this way:

a inner join b using (id)
inner join c ...
/* at this point I am joining c to the "row" created by joining a and b , so I can use columns from a AND b */

Your example would be

select A.x, B.y, C.z
from A
inner join B on ( A.x = B.x )
inner join C on ( A.x = C.x and B.x = C.x ) ;

Of course, without looking at the execution plan of the devious example you have no doubt set up, I can't tell you if this is really equivalent.

Example:

SQL> select t1.value, t2.value, t3.value   2 from t1
  3 inner join t2 on (t1.id = t2.id)
  4 inner join t3 on (t1.id = t3.id and t2.id = t3.id) ;

VALUE VALUE VALUE
---------- ---------- ----------

one        un         eins
six        six        sechs
seven      sept       sieben

(using these tables)

drop table t1 ;
drop table t2 ;
drop table t3 ;
create table t1 (id number, value varchar2 (10)) ;

create table t2 (id number, value varchar2 (10)) ; create table t3 (id number, value varchar2 (10)) ; insert into t1 (id, value)
select 1, 'one' from dual

 union all select 2, 'two' from dual
 union all select 3, 'three' from dual
 union all select 4, 'four' from dual
 union all select 5, 'five' from dual
 union all select 6, 'six' from dual
 union all select 7, 'seven' from dual ;

insert into t2 (id, value)
select 1, 'un' from dual

 union all select 3, 'trois' from dual
 union all select 5, 'cinq' from dual
 union all select 6, 'six' from dual
 union all select 7, 'sept' from dual
 union all select 8, 'huit' from dual
 union all select 9, 'neuf' from dual

union all select 10, 'dix' from dual ;

insert into t3 (id, value)
select 1, 'eins' from dual

 union all select 2, 'zwei' from dual
 union all select 4, 'vier' from dual
 union all select 6, 'sechs' from dual
 union all select 7, 'sieben' from dual
 union all select 8, 'acht' from dual
 union all select 10, 'zehn' from dual

union all select 11, 'elf' from dual
union all select 12, 'zwoelf' from dual ;

commit ;

-----Message d'origine-----
De : Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Envoyé : mercredi, 18. octobre 2006 12:55 À : Jacques Kilchoer
Cc : oracle-l
Objet : 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



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

Received on Wed Oct 18 2006 - 18:04:31 CDT