RE: should one use ANSI join syntax when writing an Oracle applic ation?
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Thu, 19 Oct 2006 10:13:18 -0700
You thought wrong and you have to read the metalink and my earlier post more
carefully. They describe two different instances of transitive closure. The
metalink note describes transitive closure from (what I call) a scalar predicate
and a join predicate:
where tblA.column = value
and tblA.column = tblB.column
and the transformation engine (AFAIK a different piece from the CBO) replaces
those two predicates with two scalar predicates
where tblA.column = value
and tblB.column = value
What I was talking about is the transitive closure for 3 equality join
predicates of 3 or more tables in a join:
where tblA.column = tblB.column
and tblB.column = tblC.column
transitive closure lets me deduce that therefore tblA.column = tblC.column but
the transformation engine and the CBO do not make that deduction and therefore
there is value in specifying the additional join predicate explicitly, even
though from a result perspective it is redundant - or replace one set of join
predicates with a different but equivalent set if you want (need) to get
creative with the CBO.
Quoting "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>:
> I thought Oracle was smart enough to perform transitive closure on its
> own even if you don't explicitly write it in your SQL, e.g. Metalink
> #68979.1:
>
> "Transitivity and Transitive Closure
> ===================================
>
> Purpose
> ~~~~~~~
> This article explains how the Cost Based Optimizer (CBO) generates
> transitive
> predicates to open potential new access methods."
>
>
--
regards
Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
