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

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_at_OneNeck.com>:

> 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

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