RE: query run time vs IN list elements

[gmei]

Title: RE: query run time vs IN list elements

Hi:

 

1. I should have mentioned that I analyzed the index after creating the index, also I looked at the explain table in both situations:

 

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=81 Card=267 Bytes=29
          37)

 

   1    0   SORT (GROUP BY) (Cost=81 Card=267 Bytes=2937)
   2    1     TABLE ACCESS (FULL) OF 'GENE2DISEASE2H' (Cost=28 Card=26
          7 Bytes=2937)

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=267 Bytes=29
          37)

 

   1    0   SORT (GROUP BY) (Cost=57 Card=267 Bytes=2937)
   2    1     INLIST ITERATOR
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'GENE2DISEASE2H' (Cos
          t=4 Card=267 Bytes=2937)

 

   4    3         INDEX (RANGE SCAN) OF 'GENE2DISEASE2H_GENEID' (NON-U
          NIQUE) (Cost=2 Card=267)

 

2. I have not tried using "exists". I was wondering why IN had such problem (I have not seen this situation before)?

 

Guang

-----Original Message-----
From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]
Sent: Friday, June 06, 2003 2:14 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: query run time vs IN list elements

Guang,

1. Just because you created an index doesn't necessarily mean Oracle is using it especially if using CBO and you haven't analyzed the table and index after the index creation. Try using a hint.

2. If IN isn't meeting your needs, try converting it to an EXISTS statement.

Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145

-----Original Message-----
From:   gmei [SMTP:[EMAIL PROTECTED]

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




  <<< cut >>>