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."

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation


Other related posts: