Re: Index not used

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 19 May 2006 11:39:24 -0700
Message-ID: <1148063964.446e10dca7097@webmail.hosting.telus.net>

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

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

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

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

Received on Fri May 19 2006 - 13:39:24 CDT