Oracle-L: too low optimizer_index_cost_adj causing bizarre index choice

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Thu, 09 May 2002 05:28:28 -0800
Message-ID: <F001.0045C87E.20020509052828@fatcity.com>

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

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: jack_silvey_at_yahoo.com

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: ListGuru_at_fatcity.com (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).

Received on Thu May 09 2002 - 08:28:28 CDT