Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: rgaffuri@cox.net (Ryan Gaffuri) Newsgroups: comp.databases.oracle.misc Subject: Re: How to optimize this query? Date: 8 Aug 2003 03:56:06 -0700 Organization: http://groups.google.com/ Lines: 98 Message-ID: <1efdad5b.0308080256.77f02868@posting.google.com> References: <1efdad5b.0308070813.55e6a746@posting.google.com> NNTP-Posting-Host: 198.80.171.28 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1060340167 25667 127.0.0.1 (8 Aug 2003 10:56:07 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 8 Aug 2003 10:56:07 GMT Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130363 "music4" wrote in message news:... > "Ryan Gaffuri" wrote in message > news:1efdad5b.0308070813.55e6a746@posting.google.com... > > "music4" wrote in message > news:... > > > 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.