SQL argument limit in Oracle

Question

It appears that there is a limit of 1000 arguments in an Oracle SQL. I ran into this when generating queries such as....

select * from orders where user_id IN(large list of ids over 1000)

My workaround is to create a temporary table, insert the user ids into that first instead of issuing a query via JDBC that has a giant list of parameters in the IN.

Does anybody know of an easier workaround? Since we are using Hibernate I wonder if it automatically is able to do a similar workaround transparently.

Answer 1

As long as the temporary table is a global temporary table (ie only visible to the session), this is the recommended way of doing things (and I'd go that route for anything more than a dozen arguments, let alone a thousand).

I'd wonder where/how you are building that list of 1000 arguments. If this is a semi-permanent grouping (eg all employees based in a particular location) then that grouping should be in the database and the join done there. Databases are designed and built to do joins really quickly. Much quicker than pulling a bunch of id's back to the mid tier and then sending them back to the database.

select * from orders 
where user_id in 
(select user_id from users where location = :loc)

Answer 2

the comments regarding "if these IDs are in your database, use joins/correlation instead" hold true. However, if your list of IDs comes from elsewhere, like a SOLR result, you can get around the temp table requirement by issuing multiple queries, each with no more than 1000 ids present, and then merging the results of the query in memory. If you place the initial list of ids in a unique collection like a hashset, you can pop off 1000 ids at a time.

Answer 3

You can add additional predicates to split the list into chunks of 1000:

select * from orders where user_id IN (<first batch of 1000>)
OR user_id IN (<second batch of 1000>)
OR user_id IN ...

Answer 4

An alternative approach would be to pass an array to the database and use a TABLE() function in the IN clause. This will probably perform better than a temporary table. It will certainly be more efficient than running multiple queries. But you will need to monitor PGA memory usage if you have a large number of sessions doing this stuff. Also, I'm not sure how easy it will be to wire this into Hibernate.

Note: TABLE() functions operate in the SQL engine, so they need us to declare a SQL type.

create or replace type tags_nt as table of varchar2(10);
/

The following sample populates an array with a couple of thousand random tags. It then uses the array in the IN clause of a query.

declare
    search_tags tags_nt;
    n pls_integer;
begin

    select name 
    bulk collect into search_tags
    from ( select name 
           from temp_tags
           order by dbms_random.value )
    where rownum <= 2000;

    select count(*)
    into n
    from big_table
    where name in ( select * from table (search_tags) );

    dbms_output.put_line('tags match '||n||' rows!');
end;
/

Answer 5

Here is a great explanation from Tom Kyte of some different approaches to varying IN lists:

http://tkyte.blogspot.com/2006/06/varying-in-lists.html

I've used his str2tbl function with very good results.