4

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;

GLinBoy
  • 606
  • 1
  • 10
  • 20
  • 1
    @Karol Dowbecki is right. There is a limit, it is there for a reason. There are ways how to get over it. But you might shoot yourself in to a leg - or DBAs will not like you. – ibre5041 Sep 17 '18 at 10:00
  • 1
    You can also pass a Java array as an Oracle collection and use that with the `MEMBER OF` operator (or a join on a table collection expression). See https://stackoverflow.com/a/34699771/1509264 or https://stackoverflow.com/q/37160300/1509264 or https://stackoverflow.com/a/51704275/1509264 – MT0 Sep 17 '18 at 10:15
  • thank you, you are right, but we need this temporary and we replace this with native query (without use JPA); then we use temp table as @karol-dowbecki saied. – GLinBoy Sep 18 '18 at 04:09
  • You should just use a subquery to select those IDs the same way you originally got them from the database. It should be faster, because then you're only sending one query for the DB to parse, rather than sending one, retrieving the data, converting to Java, passing through Hibernate in both directions, then sending a new query with over a thousand values to parse. – coladict Sep 18 '18 at 08:33

2 Answers2

6

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.

Karol Dowbecki
  • 43,645
  • 9
  • 78
  • 111
3

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! ;)

Gustavo Dias
  • 339
  • 2
  • 6