Re: Speeding up query that contains BETWEEN

"David Saracini" <dsaracini_at_yahoo.com> wrote in message news:45c4f95.0205090637.e85b997_at_posting.google.com...
> 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

The first query will use a simple index lookup. The second query, however, will either use an index range scan with the first column, street, only, or it will use the street_no, street index. I would drop the second index, it's pretty useless as you never want to know all the streets with the street no 2141, and you are bound to become a victim of the optimizer choosing the index. If you want further feedback please post the explain plan results of the two scenario's

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address