an 'in' is just an 'or' so as soon as one value in your in list is found it has accomplished its task. so if there are alot of possibilities to choose from oracle has to search less records to find one that it wants. > > 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).
