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

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@xxxxxxxxxxxxx] 
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@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

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



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

Other related posts: