Usage of indexes with greater/lower then operators

From: Maoz Mussel <maoz_at_mindcti.com>
Date: Sun, 16 May 1999 18:40:29 +0300
Message-ID: <7hmomq$39v$1@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 - 10:40:29 CDT