How to use index of choice

From: wareing t computer services <p0070621_at_oxford-brookes.ac.uk>
Date: 28 May 1993 08:36:00 -0500
Message-ID: <C7qMvz.43y_at_uk.ac.brookes>


We're using version 6.0.36 of Oracle, and have a problem with the query optimizer:

Given two indexes on a table:
1) Unique index on (A, B, C) and
2) nonunique index on (B).

If we attempt a query with a WHERE clause of:

   WHERE A=<this_value> AND B=<that_value> then, according to TRACE, only the second index is used.

Looking this up in the DBA guide reveals why: using an entire nonunique concatenated index (the second one) has a rank of 6, whilst using a nonunique index (the first two columns of the first index) has a rank of 7. (Alternatively, most leading concatenated index specified, ie. all of the first index has a rank of 9: I'm not sure whether this is a possibility.)

This makes the query run painfully slowly.

If we drop the third column from the first index (making it nonunique), it runs at about 30 times the speed, but (obviously) we lose the uninqueness.

Does anybody know how to force the use of the first index rather than the second, without causing any other problems?

Ta, in advance.

--
 _________________________   __________________________________________
/  Tommy Wareing          \ /  I've been looking for an original sin,  \
|  p0070621_at_uk.ac.brookes  X   One with a twist and a bit of a spin    |
\  0865-483389            / \     -- Pandora's Box, Jim Steinman       /
 ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Received on Fri May 28 1993 - 15:36:00 CEST

Original text of this message