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
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 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
Inserting more than 1000 rows from Excel into SQLServer
Microsoft provides an import wizard with SQL Server. I've used it to migrate data from other databases and from spreadsheets. It is pretty robust and easy to use.
Insert multiple rows into DB with Python list of Tuples
Well, you need to construct the line:
INSERT INTO ... VALUES (1,7,3000), (1,8,3500), (1,9,3900)
Try that one:
rows = [(1,7,3000), (1,8,3500), (1,9,3900)]
values = ', '.join(map(str, rows))
sql = "INSERT INTO ... VALUES {}".format(values)
Related Topics
How to Combine These Two SQL Statements
Why SQL Server Ignores Vaules in String Concatenation When Order by Clause Specified
How to Write a Query to Extract Individual Changes from Snapshots of Data
Hive - How to Further Optimize a Hiveql Query
How to Pivot Data in Bigquery Standard SQL Without Manual Hardcoding
Cascade Copy a Row with All Child Rows and Their Child Rows, etc
Split String Oracle into a Single Column and Insert into a Table
Use Plink to Execute Command (Oracle SQL Query) on Remote Server Over Ssh
I Have a Delete-Insert Cte That Fails in a Strange Manner
Which Orm Frameworks Will Build and Execute the SQL Ddl for You
SQL Server Automatic Update Datetimestamp Field
Tsql Aggregate String for Group By
Merge Duplicate Temporal Records in Database
SQL Trigger After Insert Update Another Table with Conditions
Transpose a Row into Columns with MySQL Without Using Unions
Can Vba in Ms Access Using Parameter to Prevent SQL Injection