Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Strange choice of index
Server : Oracle 8.0, optimizer is RULE by default.
Problem : according to the explain plan, in the following query,
Oracle uses the non-unique index (on component_sequence_id alone),
although another unique index exists on 1: component_sequence_id
(again), 2 : component_reference_designator and 3 : acd_type, and
criteria are provided for the first two columns.
When re-enabling the 3rd criteria on acd_type (which is not what
the user wants), Oracle uses the unique index.
SELECT COUNT(*)
FROM
bom_reference_designators d,
bom_inventory_components i
WHERE
d.component_sequence_id = i.component_sequence_id
AND d.component_reference_designator = 'C149'
--AND d.acd_type = 3
Why does Oracle choose the less discriminating non-unique index,
although it can use the 2 first columns of the unique index ?
The execution time is 4 times greater with the non-unique index as
with the unique one. The table has about 1/2 million records.
Acd_type is not discriminating (same problem with d.acd_type+0 =
3 ) and its use is almost useless.
As an experiment, I dropped the non-unique index : Oracle uses
without any problem the unique index on the 2 first columns, and
execution time is good.
Adding an hint to force the use of the unique index works, but the
execution plan is totally reversed, and it messes up the big query
from which this example is taken.
If someone has an explanation or an idea to force Oracle to use
the unique index although the last column is missing, I would be
glad to hear it. Thanks in advance !
-- Christophe Courtois - Strasbourg, France http://courtois.multimania.comReceived on Fri Dec 21 2001 - 03:05:37 CST