Actually, when filters are added to t1 then FTS on T2 is gone. Why would that be ?
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.
On 5/18/06, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > *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@xxxxxxxxxxxxx> 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@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 > > > > > -- > > 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, Ranko Mosic Contract Senior Oracle DBA B. Eng, Oracle 10g, 9i Certified Database Professional Phone: 416-450-2785 email: mosicr@xxxxxxxxxx
-- 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