How to put more than 1000 values into an Oracle IN clause
Put the values in a temporary table and then do a select where id in (select id from temptable)
SQL IN Clause 1000 item limit
You should transform the IN clauses to INNER JOIN clauses.
You can transform a query like this one
SELECT foo
FROM bar
WHERE bar.stuff IN
(SELECT stuff FROM asdf)
in a query like this other one.
SELECT b.foo
FROM (
SELECT DISTINCT stuff
FROM asdf ) a
JOIN bar b
ON b.stuff = a.stuff
You will also gain a lot of performance
Oracle SQL: How to use more than 1000 items inside an IN clause
Not sure that using so many values in a IN()
is that good, actually -- especially for performances.
When you say "the results are strange", maybe this is because a problem with parenthesis ? What if you try this, instead of what you proposed :
SELECT ...
FROM ...
WHERE ...
AND (
ep_codes IN (...1000 ep_codes...)
OR ep_codes IN (...200 ep_codes...)
)
Does it make the results less strange ?
Why oracle IN clause has limit of 1000 only for static data?
It's a restriction on any expression list:
A comma-delimited list of expressions can contain no more than 1000 expressions.
Why 1000? Presumably the implementation needs some kind of limit, and that probably seemed like more than enough. There may well be, or certainly may have been when that limit was set decades ago, a performance reason for the limit as well, particularly as the IN
is converted to multiple OR
statements by the optimiser in this case (which you can see if you look at the execution plan).
I'd struggle to come up with a reasonable scenario that needed to get anywhere near that, with fixed values that couldn't be derived from other data anyway as a subquery.
I suspect it's somewhat related to the logical database limits which say you can't have more than 1000 columns in a table, for instance; since an expression list is used in an insert statement to list both the columns and the values being inserted, the expression list has to be able to match that, but maybe has no reason to exceed it.
Speculation of course... without seeing the internals of the software you're unlikely to get a definitive answer.
IN clause limitation in Sql Server
Yes, there is a limit, but Microsoft only specifies that it lies "in the thousands":
Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.
Looking at those errors in details, we see that this limit is not specific to IN
but applies to query complexity in general:
Error 8623:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Error 8632:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
Oracle error code ORA-00913 - IN CLAUSE limitation with more than 65000 values (Used OR condition for every 1k values)
The issue isn't about in
lists; it is about a limit on the number of or
-delimited compound conditions. I believe the limit applies not to or
specifically, but to any compound conditions using any combination of or
, and
and not
, with or without parentheses. And, importantly, this doesn't seem to be documented anywhere, nor acknowledged by anyone at Oracle.
As you clearly know already, there is a limit of 1000 items in an in
list - and you have worked around that.
The parser expands an in
condition as a compound, or
-delimited condition. The limit that applies to you is the one I mentioned already.
The limit is 65,535 "atomic" conditions (put together with or
, and
, not
). It is not difficult to write examples that confirm this.
The better question is why (and, of course, how to work around it).
My suspicion: To evaluate such compound conditions, the compiled code must use a stack, which is very likely implemented as an array. The array is indexed by unsigned 16-bit integers (why so small, only Oracle can tell). So the stack size can be no more than 2^16 = 65,536; and actually only one less, because Oracle thinks that array indexes start at 1, not at 0 - so they lose one index value (0).
Workaround: create a temporary table to store your 85,000 values. Note that the idea of using tuples (artificial as it is) allows you to overcome the 1000 values limit for a single in
list, but it does not work around the limit of 65,535 "atomic" conditions in an or
-delimited compound condition; this limit applies in the most general case, regardless of where the conditions come from originally (in
lists or anything else).
More information on AskTom - you may want to start at the bottom (my comments, which are the last ones in the threads):
https://asktom.oracle.com/pls/apex/f?p=100:11:10737011707014::::P11_QUESTION_ID:9530196000346534356#9545388800346146842
https://asktom.oracle.com/pls/apex/f?p=100:11:10737011707014::::P11_QUESTION_ID:778625947169#9545394700346458835
Related Topics
Must Appear in the Group by Clause or Be Used in an Aggregate Function
Get Value Based on Max of a Different Column Grouped by Another Column
How to Group Time by Hour or by 10 Minutes
Query For Array Elements Inside Json Type
Can't Connect to MySQL Server Error 111
What's the Difference Between Inner Join, Left Join, Right Join and Full Join
MySQL Select 10 Random Rows from 600K Rows Fast
How to Do an Update Statement With Join in SQL Server
What's the Difference Between Not Exists Vs. Not in Vs. Left Join Where Is Null
Aggregate Columns With Additional (Distinct) Filters
What Is the Major Difference Between Varchar2 and Char
SQL Server 2008 Management Studio Not Checking the Syntax of My Query
Using an Alias in a Where Clause
SQL Server Process Queue Race Condition
Listagg Function: "Result of String Concatenation Is Too Long"