[Dbix-class] Regarding Oracle's 1000 element limit in IN clause
jorge.gonzalez at daikon.es
Mon Oct 24 08:39:40 GMT 2011
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
More information about the DBIx-Class