Re: Index not used

Even the following query does FTS:
SELECT *
> 2 FROM tab1 t1 ,
> 3 tab2 t2

where t1.id = t2.id

rm

On 5/17/06, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
>
> 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_at_gmail.com>:
>
> > 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_at_rogers.com
> >
>
> http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
> >
>
>
> --
> 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_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html

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