oracle 其中限制为 1000 / hibernate
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1673521/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle where in limitation to 1000 / hibernate
提问by paweloque
Oracle knows the limitation to 1000 elements in the where a in (..)clause. Does this limitation also exist when using Hibernate in conjuction with Oracle?
Oracle 知道该where a in (..)子句中对 1000 个元素的限制。将 Hibernate 与 Oracle 结合使用时是否也存在此限制？
采纳答案by Tendayi Mawushe
This database limitation still exists with hibernate. If you really need to have more than 1000 items in your in clause you will have to split the list yourself in the code and run the query for each block of 1000 keys, then append the result sets together.
这个数据库限制仍然存在于休眠状态。如果你真的需要在你的 in 子句中有超过 1000 个项目，你必须自己在代码中拆分列表并对每个包含 1000 个键的块运行查询，然后将结果集附加在一起。
Note this hack breaks down if your query needs to sort or otherwise aggregate the results of the query because the full set of results will only be known in the code. In this case you are better of finding another way to write the query that does not require an INclause.
请注意，如果您的查询需要对查询结果进行排序或以其他方式聚合，则此 hack 会失效，因为完整的结果集仅在代码中可见。在这种情况下，您最好找到另一种编写不需要IN子句的查询的方法。
回答by Patrick Hammer
We had the same issue and solved it by splitting it up into 100er packages of IN clause
我们遇到了同样的问题，并通过将其拆分为 100 个 IN 子句包来解决它
select * from mytable where id in (...) or id in (...).
Remarks:
评论：
- make sure that you use bind variables
- make sure that the query always looks the same. This is done by filling up the IN clauses with -1 until you have 100 elements in it
- try to use always the same number of ORed in(...) so that the query always looks to same
- 确保您使用绑定变量
- 确保查询始终看起来相同。这是通过用 -1 填充 IN 子句直到你有 100 个元素来完成的
- 尝试始终使用相同数量的 ORed in(...) 以便查询始终看起来相同
Why you want the points above? It is so that the query optimizer can reuse the query plan.
为什么你想要上面的点？这样查询优化器就可以重用查询计划。
Additional optimization:
额外优化：
- instead of using -1, you can just use the very last true value again. That is even a little bit better.
- 而不是使用 -1，您可以再次使用最后一个真值。这甚至更好一点。
example: in(1, 2, 3, 4, 4, ..., 4)
例如：in(1, 2, 3, 4, 4, ..., 4)
Note also: we tested with various fixed numbers of elements in the in clause and observed decreased performances for a lot than 100 elements.
另请注意：我们在 in 子句中使用各种固定数量的元素进行了测试，并观察到超过 100 个元素的性能下降。
回答by user151019
Yes as Hibernate will call Oracle at some stage so the limit is the lowest of the limits in Hibernate and Oracle
是的，因为 Hibernate 会在某个阶段调用 Oracle，因此该限制是 Hibernate 和 Oracle 中的最低限制
Hibernate does nothing special with data for an in - just passes it to the database
Hibernate 对 in 的数据没有什么特别的处理——只是将它传递给数据库
回答by Christian
It seems to be a Bug in Hibernate:
这似乎是 Hibernate 中的一个错误：
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1123
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1123
