Re: Index not used

Actually, when filters are added to t1 then FTS on T2 is gone. Why would that be ?

rm

On 5/18/06, Ranko Mosic <ranko.mosic_at_gmail.com> wrote:
>
> t is cross ref table betwen t1 and t3 , so 3rd join can't be added (
> t1.id = t3.id ).
>
> Well, I guees Mark has the right when saying that at least one table must
> be FTS as driver table in the
> absence of filters.
> This is exactly what customer noticed too - by adding additional filters
> FTS is completelly gone.
>
> Mistery solved I guess.
> Thanks all, especially Mark.
>
> Rgrds, rm.
>
>
> On 5/18/06, Bobak, Mark <Mark.Bobak_at_il.proquest.com> wrote:
> >
> > Um, ok. Why would you not expect at least one FTS? There is no filter
> > predicate, therefore, the driving table MUST do FTS. Depending on
> > statistics, the optimizer may decide that utilizing the join predicate to
> > drive a index access is efficient to join the second table to the driving
> > table, or it may decide to FTS the second table as well.
> >
> > However, in that query, at least one table MUST be full scanned.
> >
> > -Mark
> >
> >
> > *--*
> > *Mark J. Bobak*
> > *Senior Oracle Architect*
> > *ProQuest Information & Learning*
> >
> > For a successful technology, reality must take precedence over public
> > relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988
> >
> >
> > ------------------------------
> > *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> > *On Behalf Of *Ranko Mosic
> > *Sent:* Thursday, May 18, 2006 2:06 PM
> > *To:* Wolfgang Breitling
> > *Cc:* _oracle_L_list
> >
> > *Subject:* 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
> >
> >
>
>
>
> --
>
> 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,
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