在Oracle IN Operator中使用JPA和100​​0 ID [英] JPA and 1000 ID use in Oracle IN Operator

问题描述

我使用NamedNativeQuery删除行，就像这样:

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

但是我如何在Oracle的IN Operator中使用1000个以上的userIds提示呢?

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

PS:我正在寻找一种解决方案，可以一次提交来处理它；

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

推荐答案

解决IN限制的效率低下，并且JPA并非总是适合此工作的正确工具.请考虑以下内容:

Working around the IN limit is inefficient and JPA is not always the right tool for the job. Consider the following:

1. 数千个绑定值将导致潜在的兆字节SQL.将此SQL发送到数据库将花费很长时间.数据库读取SQL文本所需的时间可能比根据汤姆对限制和转换很长的IN列表:WHERE x IN(,,, ...)"问题的答案.

由于SQL解析，效率低下.解析这个长的SQL不仅花费很长时间，而且每个调用都有不同数量的绑定参数，这些参数将分别解析和计划(请参阅

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).

SQL语句中对绑定参数有硬限制.您可以重复OR几次，以解决IN限制，但有时会达到SQL语句限制.

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.

对于这些类型的查询，通常最好创建临时表.在查询之前创建一个，将所有标识符插入其中，并将其与查询中的实体表联接以模拟IN条件.

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.

理想情况下，可以用存储过程替换JPA，尤其是当您从数据库中提取成千上万个标识符只是为了在下一个查询中将它们传递回数据库时.

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.

这篇关于在Oracle IN Operator中使用JPA和100​​0 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！