Re: IN clause with more than 999 elements

From: Richard Kuhler <noone_at_nowhere.com>
Date: Thu, 18 Sep 2003 18:19:49 GMT
Message-ID: <9Hmab.3769$Sa7.453@twister.socal.rr.com>

Turkbear wrote:

> Richard Kuhler <noone_at_nowhere.com> wrote:
> 
> 

>>laSerpe wrote:
>>
>>>How can I solve problem which Oracle IN-clause doesn't work with more than
>>>999 elements?
>>>for example:
>>>I want execute a query like:
>>>SELECT * FROM TABLE WHERE FIELD IN (VALUE1,...VALUEn) with n>=1000
>>>thank you, federica
>>
>>Of course there's always ...
>>
>>FIELD IN (VALUE1, ... VALUE1000)
>>OR FIELD IN (VALUE1001, ... VALUE2000)
>>etc...
>>
>>
>>Richard Kuhler

> 
> 
> Depending on the number of records and other fields, that combination of OR and IN could still be running when the Heat Death
> Of The Universe happens... ;-)

I'm curious how you came to this conclusion. Can you provide some benchmarks or technical explanation that support this belief? My benchmarks indicate that they perform very similarly (I did tests with 2500 rows against a 12 million row table). Actually, the OR IN LIST method is always going to be faster if you factor in the time to insert the values into the temporary table.

Richard Kuhler Received on Thu Sep 18 2003 - 13:19:49 CDT