Re: Oracle Index Question

On Jan 15, 2:49 pm, Jimbo <jamesfer..._at_hotmail.com> wrote:
> I work primarily in SQL Server and have run across a situatiion in
> Oracle that's troubling me....
>
> I have a query that contains a where clause with 2 restrictions. Both
> these fields are in a composite index of 3 fields. If I add the third
> field to the restriction the optimizer uses the index, however if i
> keep it with just the 2 fields it does a table scan and takes forever
> unless I put in an index hint to use the composite index. Is this
> right?..is this a dba issue?

If the 2 restrictions are against the 2nd and 3rd columns in the index, the only option the optimizer has (in using this index) is to perform a index skip scan. If you ensure the leading (1st) column in the index is specified as one of the restrictions, then the index can be used.

Dave Received on Wed Jan 16 2008 - 09:04:06 CST