Re: Oracle Won't use INLIST ITERATOR in simple case

jim.thompson_at_lagosantafe.com wrote:
> 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.

Have you read Jonathan Lewis's latest book? Received on Thu Nov 02 2006 - 16:50:46 CST