Sorry Wolfgang and others - as I said I have to rewrite the query, that's why confustion happened.
Transitive closure doesn't work not because t2 is a xref table but because you originally misstated the query:
SELECT * 2 FROM tab1 t1 , 3 tab2 t2 , 4 tab3 t3 5 WHERE t1.id = t2.id 6 AND t2.id = t3.id 7 AND t1.type IN 8 ('A','B','C') 9 AND t3.type = 'A' 10* AND t3.start_date = '01-APR-2004'
That was quite a bit different from what you claim now. In that form transitive closure follows from logic rules. In the new form there is of course no transitive closure because the join predicates are different.
Quoting Ranko Mosic <ranko.mosic@xxxxxxxxx>:
> Correction, query looks like this ( have to rewrite the original, so little > bit of confusion here ) > SELECT * > 2 FROM tab1 t1 , > 3 tab2 t2 , > 4 tab3 t3 > 5 WHERE t1.t1_id <http://t1.id/> = t2.t1_id <http://t2.id/> > 6 AND t2.t3_id <http://t2.id/> = t3._t3id <http://t3.id/> > 7 AND t1.type IN > 8 ('A','B','C') > 9 AND t3.type = 'A' > 10* AND t3.start_date = '01-APR-2004' > Again, T2 is xref table. That's why transition can't work, Wolfgang. > For Igor, I played with stripped down version of query without filters to > see what happes. > The original query has filters. >
Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com
-- Regards, Ranko Mosic Contract Senior Oracle DBA B. Eng, Oracle 10g, 9i Certified Database Professional Phone: 416-450-2785 email: mosicr@xxxxxxxxxx http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html