Re: Index not used

There is no predicate for tab2, the smallest of the three tables, apart from the
join predicate. Are there usable indexes on tab1.type and tab3.start_date? 
Any changes if you add the transitive closure predicate (the optimizer does not
do this (yet)):
t1.id = t2.id and t2.id = t3.id => t1.id = t3.id
It opens additional access paths.

Quoting Ranko Mosic <ranko.mosic@xxxxxxxxx>:

> Hi List,
> 
> 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'
> 
> All join cols are indexed on leading cols. There is FTS on t2 . Stats are
> fresh and computed .
> Why FTS on t2 ? ( v 9.2 ).
> -- 
> 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
> 


-- 
regards

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

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


Other related posts: