Re: Usage of indexes with greater/lower then operators

What's happening ?

Oracle uses 1 index to select a subset of the data. On that subset it performs a "full scan" to
resolve your other conditions. So yes, it is normal oracle behaviour.

Gert

Maoz Mussel <maoz_at_mindcti.com> wrote in article <7hmol0$396$1_at_news.netvision.net.il>...
> Hi there,
>
> I have the following xxx table:
> id NUMBER
> lowerval NUMBER
> upperval NUMBER
> I have indexes on both fields. When I'm running a query to get rows that
> falls between
> the lower and the upper values, e.g.:
> SELECT id FROM xxx
> WHERE lowerval <= 1000 AND upperval >= 1000
>
> Only one index is used (and it is the index on the upperval column from
some
> reason).
> If xxx is a big table, indexes will not help much on such case. My
questions
> are:
> - Is this (Oracle use only one index) is a normal behavior ?
> - Any idea for different implementation of such case, e.g., getting
> specific row that
> a value is locate between lower and upper values, still with reasonable
> performance?
>
> Thanks for any info,
> Maoz
>
>
>
Received on Sun May 16 1999 - 14:45:53 CDT