Re: Speeding up query that contains BETWEEN

David Saracini wrote:

> Hello All,
>
> Is there any way to speed up a query that has a between clause. For
> example, the following query takes about 20 ms.
>
> select a.*, b.other_info
> from address a, base b
> where
> a.street_name = 'TENNESSEE ST'
> and
> a.street_no = 2191
> and
> a.street_direction = 'W'
> and
> a.rid = b.rid
> order by a.rid
>
> But, the following, with a between clause, takes 5+ seconds...
>
> select a.*, b.other_info
> from address a, base b
> where
> a.street_name = 'TENNESSEE ST'
> and
> a.street_no between 2141 and 2241
> and
> a.street_direction = 'W'
> and
> a.rid = b.rid
> order by a.rid
>
> I currently have an index defined on street_name, street_no and a
> composite index on street_no, street_name.
>
> Is there anyway to speed this up or are between clause just slow?
>
> Thanks and Best Regards,
>
> David Saracini

First the questions.

1. What version of Oracle?
2. Have you run Explain Plan to see if the indexes are being used?
3. Are your statistics kept current so the optimizer, assuming CBO, knows what it is doing?

Then the suggestion.

Assuming CBO, current statistics, and the correct index being used ... Try:

    a.street_no > 2140 and street_no < 2242

but I wouldn't expect a dramatic improvement, if any.

Daniel Morgan Received on Thu May 09 2002 - 10:17:34 CDT