Re: Index not used

Sorry Wolfgang and others - as I said I have to rewrite the query, that's
why confustion happened.

rm


On 5/19/06, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:

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

--
regards

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

Other related posts: