Re: Speeding up query that contains BETWEEN

David Saracini wrote:

> > 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
>
> Hello Daniel,
>
> Well, the questions are hard to answer than you might think! :(
> Currently, I'm using 8.1.6 for *DEVELOPMENT* and the statistics are up
> to date. But, this is going into a commerical application, so I will
> not be in control of the database version or statistics. And just as
> a note, I don't see any real diffence with:
>
> a.street_no > 2140 and street_no < 2242
>
> Any advice will be appreciated!
>
> Best Regards,
>
> David Saracini

I'm not surprised you say no difference as I indicated. But you can force updated statistics in an application.

Look at two built-in packages.

DBMS_JOB
DBMS_STATS Let one run the other once a day or once a week. Make that part of the installation configuration.

Want to get fancy you can add UTL_SMTP into the mixture and have the database email you when they exceed the license, are running out of tablespace, have invalid objects, or just to keep you from getting bored because you don't get enough email.

Daniel Morgan Received on Fri May 10 2002 - 16:16:42 CDT