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

[Martin J. Evans]

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 
> schema.
>
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.
> Regards
> J.
Martin