Re: Strange choice of index

This is the rule based optimizer working according to the list of priorities defined in the manual. (Though I'm not sure that list exists any more in the v8 manuals).

Single column index takes precedence over partial use of a multi-column index.

The fact that the multi-column index is unique if you use ALL of it is totally irrelevant - after all, the two column you do use out of the three available may have the same value for all rows in the table.

To make this work, you have to put in a complete set of hints for the entire query - any hint (other than rule) causes the CBO to kick in, so you can't just hint one little facet of what you want to do.

Your comment about the plan being reversed may be due to the fact that the default ordering of table under Rule based is bottom to top, but the default ordering under cost based is top to bottom.

--

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research.

Christophe Courtois wrote in message <9vuu0u$1klj$1_at_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 - 04:56:17 CST