Re: How to use index of choice

I (p0070621_at_oxford-brookes.ac.uk) wrote:
> 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.
 [cut]
> Does anybody know how to force the use of the first index rather than
> the second, without causing any other problems?

Thanks to those who mailed me suggestions.

So far, most of the suggestions have been of the form: Use

     WHERE A=<this_value> AND B=<that_value>||'' so that the value of B will not be looked up in the index. This does result in the first index being used, but means that only the first column of the first index can be used. This still doesn't work particularly well.

I've had one suggestion to drop the second index, and reverse the first two columns of the first index, so that queries on column B are uninhibited, but queries on column A (alone) can't use an index.

So... What we're currently intending to use is: leave the first index alone, and add column A to the second index, giving:
1) Unique index on (A, B, C) and
2) nonunique index on (B, A).

This 'goes' at quite a respectable rate (it's the fastest solution we've found), but leaves me (stylistically) unsatisfied: the second index is doesn't contain any differing information from the first. Is the stats. based optimizer in Version 7 any good?   

--
 _________________________   __________________________________________
/  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       /
 ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~