Geek Answers Handbook

Oracle where in the limit to 1000 / sleep mode

Oracle knows the limit of 1000 elements in the where a in (..) clause. Does this also have a limitation when using Hibernate in conjunction with Oracle?

+6
oracle hibernate where
source share
4 answers

This database limitation still exists in sleep mode. If you really need to have more than 1000 elements in your section, you will have to break the list yourself in the code and run a query for each block of 1000 keys, and then add a collection of results.

Please note that this hack breaks up if your query needs to sort or otherwise aggregate the query results, because the full set of results will be known only in the code. In this case, you better find another way to write a query that does not require an IN clause.

+4
source share

We had the same problem and it was resolved by dividing it into 100-sided IN offer packages

 select * from mytable where id in (...) or id in (...).

Notes:

  • make sure you use bind variables
  • make sure the query always looks the same. This is done by filling in the IN articles with -1 until there are 100 elements in it.
  • try to always use the same amount of ORed in (...) so that the query always looks the same

Why do you want the items above? This means that the query optimizer can reuse the query plan.

Additional optimization:

  • instead of using -1, you can just use the last true value again. It is even a little better.

example: in (1, 2, 3, 4, 4, ..., 4)

Please note: we tested with different fixed element numbers in the in section and observed a performance degradation for more than 100 elements.

+3
source share

Yes, since Hibernate will call Oracle at some point, so the limit is the lowest of the restrictions in Hibernate and Oracle

Hibernate does nothing special with data for in-just passes it to the database

+2
source share

This seems to be a bug in sleep mode:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-1123

+1
source share

More articles:


All Articles