Re: Usage of indexes with greater/lower then operators

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 16 May 1999 20:42:39 +0200
Message-ID: <926883803.199.0.pluto.d4ee154e@news.demon.nl>

Hi Maoz,
Of course this completely depends on the optimizer used. My guess is you are using the rule based optimiser. In theory Oracle can merge two indexes. I have never seen that rule based optimizer decides to merge indexes. If this is rule based optimizer heuristics are being used. First decision: no where clause on the primary key (assuming id is the primary key). Second decision: no equality operator in both clauses, this means range scan, whatever happens. As Oracle parses backwards, the 'rule' is simply use the index that is being hit by the optimizer first. Swap the two clauses and see what happens.
If you are using the Cost Based Optimizer, you could by specifying a hint force the optimizer to merge indexes.

What makes you think the performance is unacceptable? Did you use tkprof to see how many block gets and consistent gets are necessary? My feeling is merging two indexes will only make it worse. Both indexes probably are not too discriminating.
Assuming you have a table like this
id lowerval upperval

1  100         200
2  200         400
3 401          500

I would index the lowerval column only or the upperval column only, and you can question whether you really need that upperval column , as you can calculate it

Finally a tip: if you can use BETWEEN use between. I have seen several cases where the developer replaced the between with conditions with an and, and that was the reason the optimizer didn't use the index on the between column.

Hth,

Sybrand Bakker, Oracle DBA

Maoz Mussel wrote in message <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 - 13:42:39 CDT