ok i think i figured out what is going on. sorry for all the emails. long day...
Look at your query. You are including a count and a group by. Both are costly operations. As you increase the values in 'IN' you are increasing the size of the resultset. COUNT and group by need to work harder since the resultset they are working off of is larger. Its easier to count to 10 and then group then it is to count to 10,000 and group. -- Elapsed: 00:00:16.93 select geneid ,genegroup, count(descriptortermid) from gene2disease2H where geneid in (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 7,163450,163817, 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 , 168501) group by geneid, genegroup; > > From: "gmei" <[EMAIL PROTECTED]> > Date: 2003/06/06 Fri PM 02:09:44 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: query run time vs IN list elements > > Hi: > > Today I have something I don't fully understand. I have oracle 8173 on Sun > Solaris. I have the following query that runs pretty fast when the number of > elements in the "IN" list is small. But if I kept adding more "geneids" in > the IN list, my query time increased dramatically. Now there is no index on > any columns on the table. I got very similar results even if I created index > on gene2disease2H.geneid. So this seem to suggest this situation has not to > do with index. > > So my question is: why did I see the sigificant time increase when I only > add one more geneid? > > TIA. > > Guang > > SQL> desc gene2disease2H > Name Null? Type > ----------------------------------------- -------- ------------------ > GENEID NUMBER > GENEGROUP VARCHAR2(25) > DESCRIPTORTERMID NOT NULL NUMBER > NUMABSTRACTS NUMBER > DATESTAMP DATE > > > -- Elapsed: 00:00:08.32 > select geneid ,genegroup, count(descriptortermid) > from gene2disease2H > where geneid in > (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 > 7,163450,163817, > 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 > ) > group by geneid, genegroup; > > > -- Elapsed: 00:00:16.93 > select geneid ,genegroup, count(descriptortermid) > from gene2disease2H > where geneid in > (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 > 7,163450,163817, > 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 > , 168501) > group by geneid, genegroup; > > > > -- Elapsed: 00:00:31.97 > select geneid ,genegroup, count(descriptortermid) > from gene2disease2H > where geneid in > (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 > 7,163450,163817, > 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 > , 168501,151968) > group by geneid, genegroup; > > > -- Elapsed: 00:01:61.51 > select geneid ,genegroup, count(descriptortermid) > from gene2disease2H > where geneid in > (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 > 7,163450,163817, > 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 > , 168501,151968, > 166472) > group by geneid, genegroup; > > > -- Elapsed: 00:02:124.63 > select geneid ,genegroup, count(descriptortermid) > from gene2disease2H > where geneid in > (165383,165409,165603,166524,149490,166141,166342,166829,166832,167771,16849 > 7,163450,163817, > 165751,167437,167438,167476,164118,166054,168728,162967,164533,164545,163212 > , 168501,151968, > 166472,167771) > group by geneid, genegroup; > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: gmei > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
