oracleのin句引数の上限を回避する方法

#まえがき
oracleのin句に与える引数の上限は1000個ですが、これを1000個以上でも対応できる方法を見かけたので、久しぶりに記事を書いてみようと思いました。

#参考文献
Extending in-lists

#データ
参考文献のデータをそのまま利用させていただいております。

create table t ( x varchar2(50));
insert into t values ('stringasd');
insert into t values ('blah');
insert into t values ('more data');
insert into t values ('blah blah');
insert into t values ('some stuff with qwe in it');
insert into t values ('more with a double match on qwe');
commit;

[oracle@6d4a9d552bed ~]$sqlplus user01/ORACLE_PWD@ORCLPDB01

SQL*Plus: Release 19.0.0.0.0 - Production on 金 9月 20 21:43:17 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

最終正常ログイン時間: 金 9月  20 2019 21:01:53 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。
USER01@ORCLPDB01> select * from t;

X
--------------------------------------------------
stringasd
blah
more data
blah blah
some stuff with qwe in it
more with a double match on qwe

6行が選択されました。

経過: 00:00:00.02

クエリ

xmltable関数の引数に検索条件の文言をカンマ区切りで渡してレコードを生成したあと、単一レコードを保持しているconditionsサブクエリからクロスアプライを受けて行複写している。そのあと、検索対象のテーブルとCROSS JOINして1つの検索対象行に対して検索条件行分を直積してレコードを生成している。最後にWHERE句でパタンマッチする行をフィルタリングしている。

with
conditions as
( 
select 'string%,more%,%qwe%' search_item_list from dual 
),
rowstyle as
(
select trim(column_value) as search_item_list
from   conditions,
xmltable(('"'||replace(search_item_list, ',', '","')||'"'))
)
select dense_rank()over(order by r.search_item_list)as ptn_grp
,row_number()over(partition by r.search_item_list order by t.x)as detect_seq
, r.search_item_list,t.x
from   rowstyle r cross join t
where t.x like r.search_item_list;


      PTN_GRP    DETECT_SEQ SEARCH_ITEM_LIST     X
------------- ------------- -------------------- --------------------------------------------------
            1             1 %blah                blah
            1             2 %blah                blah blah
            2             1 %qwe%                more with a double match on qwe
            2             2 %qwe%                some stuff with qwe in it
            3             1 string%              stringasd

5行が選択されました。

クエリ2

検索条件をテーブルで管理するようにすれば、より柔軟になるのではと思った。機能別検索条件管理マスタみたいに検索条件項目を各機能ごとに単一グルーピング（一つのグループに単一項目）でも複数グルーピング（一つのグループに複数項目）でも管理するようにすれば、ダイナミックで面白いものができそうだとおもった。機能別検索条件管理マスタのメンテナンス画面とかつくり、どの画面もこのマスタを参照するように設計すれば、柔軟な画面レイアウト、検索機能を生み出せるとおもった。同じシステムの保守の仕事を2年ちょっとぐらいしているので、改修ができるだけ少なく済むような設計がいいよなと思うことがままある。

create table search_criteria ( x varchar2(50));
insert into search_criteria values ('string%');
insert into search_criteria values ('%blah');
insert into search_criteria values ('%qwe%');
commit;

USER01@ORCLPDB01> select * from search_criteria;

X
--------------------------------------------------
string%
%blah
%qwe%

3行が選択されました。

経過: 00:00:00.02

ハードで書いた部分をテーブルから取得するように書き替えた。グルーピングする必要が出てきたら、XMLAGG関数をLISTAGG関数で置き換える。

with
conditions as
( 
select rtrim(xmlagg(xmlelement(e,x,',').extract('//text()')).getclobval(),',') as search_item_list from Search_criteria
),
rowstyle as
(
select trim(column_value) as search_item_list
from   conditions,
xmltable(('"'||replace(search_item_list, ',', '","')||'"'))
)select dense_rank()over(order by r.search_item_list)as ptn_grp
,row_number()over(partition by r.search_item_list order by t.x)as detect_seq
, r.search_item_list,t.x
from   rowstyle r cross join t
where t.x like r.search_item_list;

      PTN_GRP    DETECT_SEQ SEARCH_ITEM_LIST     X
------------- ------------- -------------------- --------------------------------------------------
            1             1 %blah                blah
            1             2 %blah                blah blah
            2             1 %qwe%                more with a double match on qwe
            2             2 %qwe%                some stuff with qwe in it
            3             1 string%              stringasd

5行が選択されました。

#あとがき
in句といえば、pivot句のやつが思い出される。（カラムの数を柔軟に変える）
以上、ありがとうございました。