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 selection problem (10053 event)

Re: index selection problem (10053 event)

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 18 May 2006 05:38:58 -0600
Message-Id: <6.2.3.4.2.20060518052908.053cf900@pop.centrexcc.com>


Yes, the answer is

  1. Because by equal cost of many indexes the CBO is using the first one in alphabetital order

The RBO used "least recently created" as a tiebreak between indexes, the CBO uses alphabetical order.
Jonathan Lewis did demonstrate that in his usual entertaining way at a Hotsos Symposium (the 2005 I believe) "proving" the CBO is conditioned to use a "Larry" index over a "Microsoft" index.

See also http://www.dbazine.com/oracle/or-articles/jlewis18

At 04:57 AM 5/18/2006, Milen Kulev wrote:
>Hello listers,
>
>I have the following situation. A problematic SQL statement (see the end of
>the posting) is not using the right index
>The index details (an excerpt from 10053 event trace file ) are below
>
>
> The cost of TD_SST_HO_ADDR_SADLR_IDX and X_SST_HO_ADDR_IN_DT_TDNUM_IDX is
>the same (Cost = 4).
> CBO prefers usage of TD_SST_HO_ADDR_SADLR_IDX index and this is not what I
>want (the other index is really better, I have tried it!)
>
>
> Workaround:
> ~~~~~~~~~~~~
> I have dropped the index X_SST_HO_ADDR_IN_DT_TDNUM_IDX and just re-created
>it with another name (IDX_SST_HO_ADDR_IN_DT_TDNUM) so that
> the new name is alphabetically "lower" than than the name of the "wrong"
>index(TD_SST_HO_ADDR_SADLR_IDX):
>
>
> The question :
> ~~~~~~~~~~~~~~~~
> Why CBO is using the first index (TD_SST_HO_ADDR_SADLR_IDX) and not the
>(better) second one (X_SST_HO_ADDR_IN_DT_TDNUM_IDX)?
> Suggestions:
> 1) Because by equal cost of many indexes the CBO is using the first one in
>alphabetital order
> 2) Because CBO thinks that "index (equal)" access is always better than
>"index (scan)" ? (some kind of internal CBO rule)
>
> It ***seems*** to me that the right answer is 1)
>
>Any opinions are highly appreciated.
>Many thanks in advance.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 18 2006 - 06:38:58 CDT

Original text of this message

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