JPA 和 1000 ID 在 Oracle IN Operator 中的使用 [英] 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 中使用超过 1000 个用户 ID 和 IN 运算符?

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

P.S:我正在寻找一种解决方案来在一次提交中处理它；

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 发送到数据库需要很长时间.根据 Tom 对限制和转换非常长的 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.

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