Undocumented behavior: setting optimizer_index_cost_adj = 1 is simply begging the optimizer to use any index, just kidding :}
I think it's simply b/c the total cost which is = cost of sequential read * number of reported rows in the index (adjusted by optimizer_index_cost_adj) is the lowest calculated one. Regards, Waleed -----Original Message----- Sent: Thursday, May 09, 2002 5:06 PM To: Multiple recipients of list ORACLE-L choice Hi Johnathan, here is the skinny: db_file_multiblock_read_count = 64 number of rows=15m blocks=251071 empty_blocks=0 db_block_size=16384 total plan cost=4924 tablescan cost = 4924 Jack --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > There seems to be a perfectly good theoretical > reason > for this. But it would be interesting to know: > Number of blocks below HWM > Setting for db_file_multiblock_read_count > Maximum usable value for db_file_mbrc > The cost given by Explain Plan for the tablescan > > before I confuse the issue further by expounding a > hypothesis that may be totally misleading. > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Building Efficient Databases > > Next Seminar - Australia - July/August > http://www.jlcomp.demon.co.uk/seminar.html > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > -----Original Message----- > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > Date: 09 May 2002 14:30 > > > |Good morning listers, > | > |Some weeks ago I noticed a query plan that was > using a > |bizarre index choice and opened a TAR. Here is that > |query: > | > |select DSS_EMPR.V_CLAIM_EMPR_HX.AMT_COPAY from > |DSS_EMPR.V_CLAIM_EMPR_HX where > |DSS_EMPR.V_CLAIM_EMPR_HX.BATCH_NUM between > |to_date('2000-01-01','yyyy-mm-dd') and > |to_date('2000-01-31','yyyy-mm-dd'); > | > |Here is the query plan: > | > | SELECT STATEMENT (all_rows) Cost > |(4924,636953,12739060) > | > | 1 0 1 2 > | TABLE ACCESS (analyzed) DSS_EMPR > T_CLAIM_EMPR_HX > |(by index rowid) Cost (4924,636953,12739060) > | > | 2 1 1 > | BITMAP CONVERSION (to rowids) > | > | 3 2 1 > | BITMAP INDEX DSS_EMPR X_CLAIM_EMPR_HX_N01 > |(full scan) > | > | > |The index x_claim_empr_hx_n01 is on the phmcy_gid > |column of the t_claim_empr_hx table. > | > |Now, here is the crux of the matter: phmcy_gid is > |referred to nowhere in the query, not in select nor > in > |where nor even in order by. > | > |Worked through the tar with Oracle and they advised > |that Oracle can and will cost ALL indexes during a > |plan parse and eval, so it became a matter of > |discovering why the index was being incorrectly > |costed. > | > |Remembered that our optimizer_index_cost_adj was > set > |to 1 (don't ask). When I upped this value to 2 or > more > |and reran the query, it returned the appropriate > FTS > |plan. > | > |hth, > | > |Jack Silvey > | > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __________________________________________________ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
