Re: How to optimize this query?

Originally posted by Music4
> 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

This might be a candidate for bitmap indexes - but read up on them first:

http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/server. 920/a96524/c11schem.htm#5381

--
Posted via http://dbforums.com