Re: How to optimize this query?

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0308050245.39bf6ad4_at_posting.google.com>...
> "music4" <music4_at_163.net> wrote i
>
> > select some field from tbl where flag1=0 and flag2=1 and flag3=2 and flag4=3
> > and flag5=4
> >
> > flag1, flag2 and flag3 are all checked in (0,1,2,3,4,5).
> >
> > And I have a index on flag1, flag2 and flag3 since flag4 and flag5 in more
> > case are constants(3 and 4).
> >
> > When tbl has huge number of data (over a half million records), I find that
> > if tbl contains data meet query condition, the select performs well. But if
> > not, the select cosumes huge resource.
> >
> > Do you have any idea to optimize this kind of query? I am using Oracle
> > 8.1.5.
>
> Dammit. My databases are still too fast...
>
> I populated a test table on DEV with half a million rows (tiny by
> today's standards), generating random numbers (between 1 and 9) for
> flag's 1 to 5.
>
> Then I created bitmap indexes on each flag column. I was thinking ito
> of bitmap merge joins or something...
>
> CBO does not even use the indexes. Does a full table scan for a
> 'SELECT count(*)'. And returns the result with a subsecond response.
>
> Sorry Evan, I'm not of much help. I have this silly problem with
> Oracle being too fact on the HP-UX boxes I'm looking after. Able to
> process 1TB of data and 2+ billion rows in less than 12 hours.. even
> though the database is 80GB and the table has around 70 million rows.
> But then does not stop my fearless developers of stress testing the
> databases with freaky SQL.
>
> When a query is slow, consider doing the following:
> - explain plan on the query
> - looking at the wait events for the session running the query
> - looking at the stats for the session running the query
>
> Throw together. Stir well. Simmer for some time, adding a teaspoon of
> logic every now and then.

Billy,

when testing for performance, don't use COUNT(). Make the query fetch rows. (COUNT() can be optimised such that no rows are fetched, making the query SEEM fast.)

So make your query something like:
select extra_column, rowid
from x where flag....

  Ed Received on Tue Aug 05 2003 - 10:58:24 CDT