Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Speeding up query that contains BETWEEN

Re: Speeding up query that contains BETWEEN

From: Alan <alanshein_at_erols.com>
Date: Thu, 9 May 2002 11:46:02 -0400
Message-ID: <abe5dh$hf18i$1@ID-114862.news.dfncis.de>


Also, if using RBO, then rearrange the query so that street_name appears last in the WHERE clause (assuming that is the most restrictive element). Actually, I would swap positions with the street_direction.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:udl5orisum7j91_at_corp.supernews.com...
>
> "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
>
>
Received on Thu May 09 2002 - 10:46:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US