query run time vs IN list elements

[gmei]

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).