Oracle Won't use INLIST ITERATOR in simple case

From: <jim.thompson_at_lagosantafe.com>
Date: 2 Nov 2006 14:40:13 -0800
Message-ID: <1162507213.224528.169680@m73g2000cwd.googlegroups.com>

In Oracle 9.2.x queries of this form will only use the INList iterator when the number of items
in the list is around 250 or so. The limit of an IN clause is 1000.

The table in question has 275,000 rows. If it does tablespace scan it takes 1 to 3 minutes.
If INLIST is used 0.150 seconds.

If I remove statistics from the index but leave them on the table -- Inlist is used.

uuid is unique key of table.

I have tried many hints, which seem to be ingnored. What a poor feature that is!

SELECT TradePilot.Libra_Collection01.uuid,

TradePilot.Libra_Collection01.enywareid,
TradePilot.Libra_Collection01.dbState,
TradePilot.Libra_Collection01.label,
TradePilot.Libra_Collection01.isEmptyFlag,
TradePilot.Libra_Collection01.objectVersion

         FROM TradePilot.Libra_Collection01
         WHERE ((TradePilot.Libra_Collection01.uuid IN


((HEXTORAW('75B17AD392C90A4CB02260D1D4D2FAA8')),

            (HEXTORAW('F5F99289CD1A194B8DB804EB82642F66')),


(HEXTORAW('0F8C2091D042B04882C341404C067612')),

(HEXTORAW('ED27DFF2D4DE8547B42BA398149B7D94')),

(HEXTORAW('E1DD983DCE25E441876D29AE1304DD37')),

(HEXTORAW('24FA0FF719070648B221A053C0F5130A')),

(HEXTORAW('E9C0FE99CC950E4C9743111A4A01CBCF')),

(HEXTORAW('3608FFBA1C26D04C88BB7EFB257E9282')),
(HEXTORAW('40F6086C1E59E141A8C026037E8BA6B4')),
Soapbox on

The entire idea that the database is always smarter needs to die. The amount of time I've spend trying hints, etc is ridiculous. I've had this problem with every RDB.

I'd really like to see some way to list tables that tablespace scans are not allowed. It would be invaluable for application development to determine performance problems immediately.

Soapbox off

If you have any suggestions -- well thanks in advance. Received on Thu Nov 02 2006 - 16:40:13 CST