CODEWITHSUNDEEP
javaoracle-databasehibernatejpaoracle11g
GLinBoy
GLinBoy

Reputation: 676

JPA and 1000 ID use in Oracle IN Operator

I use NamedNativeQuery for delete rows, and it's somthing like this:

DELETE from FAKTOR
 where ID IN (
   select fa.ID
   from FAKTOR fa
     left join FAKTOR_REASON fars
       on fa.FARS_ID = fars.ID
   where fars.ID = 63
         and fa.USER_ID in (:userIds))

But How i can use more that 1000 userIds with IN Operator at Oracle where clues?

P.S: I'm looking for a solution to handle it in one commit;

Upvotes: 6

Views: 5378

Answers (2)

Gustavo Dias
Gustavo Dias

Reputation: 359

I suggest the solution of partitioning the userIds list in your java code. Google Guava has the Lists.partition method, where you can do:

List<List<Integer>> userIdsParts = Lists.partition(userIds, 1000);

Then you can concatenate each part of in's operator with OR:

where ...
and (fa.USER_ID in (:userIdsPart1) or fa.USER_ID in (:userIdsPart2) or ...)
...

I suggest do it with query builders, like QueryDSL, to do this logic, because you usually don't know the size of userIds, then you don't know how many parts the list will have.

I hope I help you! ;)

Upvotes: 3

Karol Dowbecki
Karol Dowbecki

Reputation: 44952

Working around the IN limit is inefficient and JPA 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.

Upvotes: 8

Related Questions