[Dbix-class] Regarding Oracle's 1000 element limit in IN clause

[Jorge Gonzalez]

El 22/10/11 13:17, Martin J. Evans escribió:
>
>> For this I tried the following query: SELECT * FROM BIGTABLE WHERE 
>> FIELD IN (...~40.000 values...). Oracle, though, has a 1000 element 
>> limit for IN clauses, so I have to break the query in some 40 smaller 
>> queries and then process the results afterwards (I call this the 
>> "slice technique" since I break the list in slices with splice :-) 
>> Appart from having to do N/1000 queries insted of 1 (and now N is 
>> 40.000 but it will surely grow, so more queries), I can't offload 
>> data processing to the SQL server (which of course would be good) and 
>> my app has to process the data itself.
>>
> It would be a long piece of SQL but you could get all the results in 
> one piece of SQL with:
>
> select * from table where column in (1000 values) or column in 
> (another 1000 values) ...
>

Thanks for this useful response, I'll try it. It comes to mind that 
there was a limit on SQL query length, but although there was a 64K 
limit in Oracle 9, there seems to be no limit on Oracle 10 and onwards 
(checked in OTN doc). And my Oracle DB is 10g...

I'll keep you informed :-)
Cheers and thanks again
J.