Lazy Diary @ Hatena Blog

PowerShell / Java / miscellaneous things about software development, Tips & Gochas. CC BY-SA 4.0/Apache License 2.0

IN clause with 100,001 entries in Oracle

Background

You will get the following error when you specify over 1000 entries in IN clause in Oracle:

ORA-01795: maximum number of expressions in a list is 1000

This is by design, and the following document says "You can specify up to 1000 expressions in expression_list." docs.oracle.com

There are some hacks to bypass this constraint. If you want to write IN clause like this:

SELECT NAME TBL_A FROM TBL_A WHERE ID IN ('1', '2', ..., '999', '1000', '1001');

You can write it with tuples with dummy values like this:

SELECT NAME TBL_A FROM TBL_A WHERE (ID, 1) IN (('1', 1), ('2', 1), ..., ('99999', 1), ('100000', 1), ('100001', 1));

stackoverflow.com

Question

I had a question about how many entries you can specify in IN clause with the tuples. In StackOverflow, some say the limit is 100,000*1, and some say 70,000*2, and there is no clear evidence.

Experiment

So, let's experiment. I installed Oracle on my machine. The version is:

SQL> SELECT BANNER_FULL FROM v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

And made a table like this:

CREATE TABLE TBL_A (ID CHAR(4), NAME VARCHAR2(20));
INSERT INTO TBL_A VALUES ('1', 'JOHN DOE');

And run this PowerShell script. This script creates a SELECT statement and copies it in the clipboard. Note that you cannot paste the result of this script into sqlplus because the line breaks are ignored by sqlplus. You have to paste and re-copy it in some text editor and paste it to sqlplus. You can change the number of entries with the sequence in the head of this script. If you specify 100000..100001, you will get two SELECT statements with 100000 entries and 100001 entries.

100000..100001 | ForEach-Object { "SELECT NAME, " + $_ + " TBL_A FROM TBL_A WHERE (ID, 1) IN (" + (((1..$_) | ForEach-Object { $crlf = ''; if ($_ % 100 -eq 1) { $crlf = "`n" };  $crlf + "('" + $_ + "', 1)" }) -join ", ") + ");" } | Set-Clipboard

Result

In my environment, the IN clause with 100,000 entries, 100,001 entries, 120,001 entries are all successfully executed in sqlplus. At least, "up to 100,000 entries" seems wrong in Oracle 21c.

For someone who wants to do a replication study, I have uploaded the SQL statement with 100,001 entries in Gist. SELECT with IN clause that contains 100,001 entries for Oracle 21c · GitHub

Of course, the performance is terrible. It takes some minutes to execute. In my environment, it takes 03:57.67 for 100,000 entries, and 5:18.24 for 120,001 entries. You had better rewrite the statement with EXISTS clause for performance.

I've got ORA-03113 error when I tried the IN clause with 200,000 entries. This error seems caused by the length of the SQL statement rather than the number of entries in IN clause. The error is resolved when I change the literals in IN clause to a shorter string.

Conclusion

To conclude, in Oracle 21c, the maximum number of entries you can specify in IN clause with tuples remains unknown. At least, you can specify 200,000 entries. I could not find the upper limit in the official Oracle document, so this number would be changed without notice.