[Dbix-class] Regarding Oracle's 1000 element limit in IN clause
Martin J. Evans
martin.evans at easysoft.com
Sat Oct 22 11:17:51 GMT 2011
On 22/10/2011 11:36, Jorge Gonzalez wrote:
> Hi all,
> first of all, I think this question is probably offtopic in this list
> since it's not DBIx::Class specific, but since I do all my DB
> development with DBIx::Class I'd like to ask here first. The question
> is probably of interest for others anyway. This is it:
> I have read-only access to an Oracle schema. The user is limited, it
> can't create temporary tables, views, etc. Just access to some tables.
> I have a big table BIGTABLE in this schema (~millions of rows) which I
> want to filter by values in a specific field. The number of values to
> filter is also big (~40.000), and can't be obtained from other tables,
> i.e. I take them out of another data source which is not the Oracle
Obviously if you could pick the values from somewhere else in the
database you could do
select * from table where column in (select column from another_table)
but it sounds like you cannot do this.
> 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) ...
> My question is: has anyone managed to get past this limitation in any way?
> One approach could be to load some temporary table with the filter
> data and then join BIGTABLE with the temp table, but as I said, my
> Oracle user is very limited and can't create temporary tables, much
> less load them with data.
Which is probably what I would have done - shame.
> I'd very much appreciate any idea for solving this. And again, accept
> my apologies if you feel this question is very offtopic.
> Thanks in advance.
More information about the DBIx-Class