Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index not used

RE: Index not used

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 16 May 2006 17:56:09 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF2707F62A60@AABO-EXCHANGE02.bos.il.pqe>


Oh, I knew I forgot something:  

You mentioned that when you specified the index hint, cardinality dropped significantly. But, that's still in the realm of CBO cost calculations. How many buffer gets does the query do with the FTS? how many with the index being hinted?  

Also, are your stats up to date?  

-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 Bobak, Mark
Sent: Tuesday, May 16, 2006 5:53 PM To: ranko.mosic_at_gmail.com; Ric Van Dyke Cc: _oracle_L_list Subject: RE: Index not used Hmm....those seem to be high clustering factors, but, to be sure, we need to know how many rows are in t2, and how many blocks in t2 are below the HWM. The clustering factor will always range between the number of blocks below the HWM for the table (good) to number of rows in the table (bad). Before anyone else attempts to go there, let me just say it: Rebuilding the index(es) will NOT affect the clustering factor! What's the execution plan look like? What's the join order? Looks like you have filter predicates on t1 and t3. Are the columns which have the filter predicates indexed well? What kind of cardinality do you see from t1, based on the filter predicates? What about t3? Whichever produces the smaller rowsource is the likely candidate to be the driving table. On another note, as far as the select list goes, do you really need all columns from all tables? If your select list was 't1.*,t3.*', the horrible clustering factor on the t2 indexes would no longer have an effect on the optimizer. Just some thoughts, but really, need to see the execution plan and some answers to the questions posed above. Hope that helps, -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: Tuesday, May 16, 2006 5:36 PM To: Ric Van Dyke Cc: _oracle_L_list Subject: Re: Index not used Correction, query looks like this: SELECT * 2 FROM tab1 t1 , 3 tab2 t2 , 4 tab3 t3 5 WHERE t1.id <http://t1.id/> = t2.id <http://t2.id/> 6 AND t2.id <http://t2.id/> 1 = t3.id <http://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_at_hotsos.com> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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 <http://t1.id/> = t2.id <http://t2.id/> 6 AND t2.id <http://t2.id/> = t3.id <http://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/ContractSeniorOracleDBARankoMo sicMain.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/ContractSeniorOracleDBARankoMo sicMain.html -- http://www.freelists.org/webpage/oracle-l
Received on Tue May 16 2006 - 16:56:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US