Even the following query does FTS: SELECT *
2 FROM tab1 t1 , 3 tab2 t2
where t1.id = t2.id
rm
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