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

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 2

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)