Spring Data Repository Dynamic finder Oracle In clause max of 1000

Question

I'd like to know if there is a mechanism for handling a collection of more than 1000 items into a Spring Repository for a SQL IN clause. Right now, we are splitting the list of items before passing it to the repository, however it would be nice if the driver or spring were aware of the limitation of Oracle, and would do that work for us.

Answer 1

Working around the IN limit is inefficient, Spring Data is not always the right tool for the job. Consider the following:

1. Thousands of bound values will result in potentially megabytes of SQL. It will take a long time to send this SQL to the database. The Database might take longer to read the SQL text than execute it as per Tom's answer to "Limit and conversion very long IN list: WHERE x IN ( ,,, ...)" question.

2. It will be inefficient due to SQL parsing. Not only does it take a long time to parse this long SQL but each invocation has a different number of bound parameters which will be parsed and planned separately (see this article which explains it).

3. There is a hard limit of bound parameters in a SQL statement. You can repeat the OR a few times to work around the IN limit but you are going to hit the SQL statement limit at some point.

For those types of queries, it's usually better to create temporary tables. Create one before your query, insert all the identifiers into it and join it with the entity table in your query to simulate the IN condition.

Ideally, you can replace the JPA with a stored procedure, especially if you are pulling out tens of thousands of identifiers from the database just to pass them back to the database in the next query.

Answer 2

1. Create a temporary table:

   CREATE GLOBAL TEMPORARY TABLE table_name
   ( value column_data_type
   ) ON COMMIT DELETE ROWS;
   

   Your code inserts records into the temp table for all values used for IN clause.

   Instead of listing all values in IN, you can use IN (select value from table_name).

2. You might have complications due to session or transaction management in your container. If that could be not handled easily, you can create a normal table with an identifier

   CREATE TABLE table_name
   ( identifier number, value column_data_type
   );
   

   You also need a sequence to generate the identifier.

   Your code inserts records into the table for all values used for IN clause with a specific identifier.

   You can use IN (select value from table_name where identifier = ?).