Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news-out.visi.com!petbe.visi.com!ash.uu.net!netnews.proxy.lucent.com!news From: "music4" Newsgroups: comp.databases.oracle.misc Subject: Re: How to optimize this query? Date: Fri, 8 Aug 2003 14:47:24 +0800 Organization: Lucent Technologies Lines: 89 Message-ID: References: <1efdad5b.0308070813.55e6a746@posting.google.com> NNTP-Posting-Host: 135.252.33.149 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4922.1500 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130355 "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