Large # Items in IN () Very Slow

From: Jonathan D. Trumbull <Jonathan.Trumbull_at_abbott.com>
Date: 29 Oct 2002 15:39:50 -0800
Message-ID: <266c2da3.0210291539.4d11f1db@posting.google.com>

Hi,

I have searched and searched but I can't find even a hint of a reference on this.

Basically I have a query in the form:

SELECT ColumnA,ColumnB
FROM SomeTable
WHERE ColumnA IN ('Item1','Item2',....'ItemN')

If I have many items, say >1000 my query slows to a crawl. It takes ~2 minutes on a very very lightly loaded server. I have an nonunique index on ColumnA. Looking at the explain plan, it is using that index (although a table scan should also be fast since it's now only 40,000 rows long). For fun, I built the table as a IOT and it's still just as slow (the explain plan uses a range scan of the index?).

There is very little disk activity it just nails the processor at 98%.

My tests are being done on a single processor intel box with 512M of memory (lots free) with 8.1.7 SE and WIN2K. I am using this box as a test setup because I am seeing the same problem on a 4-processor production DB.

Any ideas what could be slowing this down?

Here is the TKPROF.

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        1      0.00       0.00          0        896         14  
        1

------- ------ -------- ---------- ---------- ---------- ----------

total        3      0.00       0.00          0        896         14  
        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5 Received on Tue Oct 29 2002 - 17:39:50 CST