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.
