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)
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 ?
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
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.
Related Topics
Ora-00054: Resource Busy and Acquire with Nowait Specified
How to Drop All Foreign Key Constraints in All Tables
Truncate All Tables in MySQL Database That Match a Name Pattern
Change Column Types in a Huge Table
Arithmetic Overflow Error Converting Numeric to Data Type Numeric
Use String Contains Function in Oracle SQL Query
Flattening Intersecting Timespans
SQL Server Permissions on Stored Procs with Dynamic SQL
How to Use Elasticsearch to Get Join Functionality as in SQL
How to Analyse a SQLite Query Execution
What Are Indexes and How to Use Them to Optimize Queries in My Database
How to Store More Than 255 Char in MySQL Database
Count Rows Per Hour in SQL Server with Full Date-Time Value as Result
My Select Sum Query Returns Null. It Should Return 0
Difference Between Decimal and Numeric