Re: Index not used
- From: "Ranko Mosic" <ranko.mosic@xxxxxxxxx>
- To: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- Date: Tue, 16 May 2006 17:35:49 -0400
Correction, query looks like this:
SELECT *
2 FROM tab1 t1 ,
3 tab2 t2 ,
4 tab3 t3
5 WHERE t1.id = *t2.id* <http://t2.id/>* *
6 AND *t2.id* <http://t2.id/>*1* = t3.id
7 AND t1.type IN
8 ('A','B','C')
9 AND t3.type = 'A'
10* AND t3.start_date = '01-APR-2004'
FTS is probably not the right thing to do - when I put index hint
cardinality went down dramatically.
Clustering_factor for index on t2.id is 110582943 and on t2.id1 is 42661968
Traces are coming if I can get them .
Regards, Ranko
On 5/16/06, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx> wrote:
A full table scan might be the right thing to do. What's the 10046 and
10053 traces look for the run of the plan? What's the Cluster Factor on the
indexes?
Ric Van Dyke
Hotsos Enterprises
Cell 248-705-0624
-----------------------
Hotsos Symposium March 4-8, 2007. Be there.
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Ranko Mosic
*Sent:* Tuesday, May 16, 2006 4:36 PM
*To:* _oracle_L_list
*Subject:* Index not used
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,
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
Other related posts:
