Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Strange choice of index

Strange choice of index

From: Christophe Courtois <christophe_at_courtois.cc>
Date: Fri, 21 Dec 2001 10:05:37 +0100
Message-ID: <9vuu0u$1klj$1@norfair.nerim.net>


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.com
Received on Fri Dec 21 2001 - 03:05:37 CST

Original text of this message

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