Re: How to optimize this query?

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0308070813.55e6a746_at_posting.google.com...
> "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.

The application runs on a Sun Netra 1400 server that has two 440MHz CPU and 1 18G harddisk. When I said "consume alot of resources", I meant that the corresponding oracle process occupy more than 20% CPU resource, and caues system IO Wait to about 20%.

Since we have only Oracle 8.1.5 standard edition, bitmap index feature is not available.

The exact query like:

select count(*) from tbl_1 where flag1=1 and flag2<>0 and flag3<>-9 and flag4=101 and flag5<30000001

  COUNT(*)

         0

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TBL_1'
   4    3         INDEX (RANGE SCAN) OF 'IND_TBL_1' (NON-UNIQUE)

Statistics

          0  recursive calls
          0  db block gets
        694  consistent gets
          0  physical reads
          0  redo size
       1090  bytes sent via SQL*Net to client
        791  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Do you have any idea on that?

Thanks,
Evan Received on Fri Aug 08 2003 - 01:47:24 CDT