Re: How to optimize this query?

"music4" <music4_at_163.net> wrote in message news:<bgvh44$s29_at_netnews.proxy.lucent.com>...
> "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

you have 694 consistent gets. there is no way this query is using up a ton of resources. something else is going on. your looking in the wrong place. Your also only sending 1090 bytes to the client. that is nothing.

anyway....

your doing 2 index scans. your hitting 'TBL_1', and 'IND_TBL_1' make a 5 column index with all the fields involved. Received on Fri Aug 08 2003 - 05:56:06 CDT