OracleでIN句に100,001個のエントリを指定する

背景

OracleでIN句の内容が1000個を超えると以下のエラーが発生する。

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

これはOracleの仕様で、以下のドキュメントに"You can specify up to 1000 expressions in expression_list."と記載されている。 docs.oracle.com

この制約を回避するハックとして、

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

とする代わりに

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

のようにダミーの値を含んだタプルをIN句に指定する方法が知られている。 stackoverflow.com

問題

ここで、タプルを指定した場合にIN句の中に指定できる内容の上限は何か、という問題がある。stackoverflowのコメントでは、最大100,000個*1と言われみたり、最大70,000個*2だと言われてみたりはっきりしない。

調査方法

じゃぁ試してみよう、ということで、手元にOracleを入れて試してみた。Oracleのバージョンは以下。

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

こんな感じでテーブルを作り……

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

こんな感じのPowerShellで、SELECT文をクリップボードにコピーする。注意として、この内容をそのままWindowsのsqlplusにペーストしても、改行が無視されて途中で内容が途切れてしまう。いちどテキストエディタ等にペーストしてから、再度コピーしてsqlplusにペーストしよう。スクリプトの最初のシーケンスを変更すれば複数のSELECT文を作ることができる。この場合、IN句が100,000個と、100,001個のSELECT文がクリップボードにペーストされる。

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

実行結果

試しに実行してみたところ、IN句が100,000個のケース・100,001個のケース、120,001個のケースはいずれも問題なく実行できた。少なくとも、100,000個が上限というのはOracle 21cでは誤りのようだ。試したい人のために、実際に使ったSQL文（100,001個のケース）を以下のGistにアップロードした。

SELECT with IN clause that contains 100,001 entries for Oracle 21c · GitHub

もちろん性能は非常に悪く、分単位の時間がかかる。実測したところ、手元では100,000件のケースで03:57.67、120,001件のケースで5:18.24かかった。性能を考慮するなら、可能であればEXISTS句＋副問い合わせの形に書き換えるのがよいだろう。

200,000個のケースでは、sqlplusから実行しようとしたところORA-03113エラーが発生したが、これはIN句の個数ではなくSQL文の長さが問題のようで、IN句に指定する定数の長さを短かくしたところ問題なく実行できた。