Re: How to optimize this query?

"music4" <music4_at_163.net> wrote in message news:<bgn6st$civ_at_netnews.proxy.lucent.com>...
> Greetings,
>
> I have this query:
>
> 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.
>
> Thanks in advance!
> Evan

please post the exact query and explain plan including statistics What do you mean by consume alot of resources? how is performance?

also please include an explain plan when data is returned. andrew may be right. try a bitmap index if this table does not have transactions on it.

500k records is not that many with the hardware we have today. What kind of system are you on?

I need more details. Received on Thu Aug 07 2003 - 11:13:47 CDT