LoginSignup
7
9

More than 3 years have passed since last update.

@tlokweng

Oracleでカンマ区切りの文字列カラム(Jaywalking)に遭遇したときの対処法

Last updated at Posted at 2018-03-09

有名なアンチパターンであるカンマ区切りで複数のアイテム値を格納した文字列カラム（Jaywalking）に遭遇したときの”一時的”な対処法です。まぁある意味SQLパフォーマンスチューニングと言えなくもないです。

Jaywalkingにはさまざまな問題点が知られていますが、一番の問題は検索パフォーマンスでしょう。カンマ区切り文字列に含まれるアイテム値を検索しようとすると前方及び後方に％をつけた部分一致検索（eg. LIKE '%,AAA,%'）となりますが、これはテーブルに対して（他に条件がなければ）基本全件検索となります。

おそらく数件の検索であればなんとかなるかもしれません。しかし、他のテーブルと結合して数千、数万に及ぶ個々のアイテム値を検索をしなければならなくなることも時にはあります。部分一致検索はその性質上、ハッシュ結合もソートマージ結合も使えません。かならずネスト結合です。つまり検索するアイテム値の数だけ全件検索を繰り返さなければならないのです。

このような状況に陥ったときの対処法は、カンマで区切ったアイテム値を一行につき一つずつのアイテム値に「その場」で「高速」に展開してやることです。その結果、等式条件のハッシュ結合が使えるようになり、アイテム値を展開するオーバーヘッドを考慮しても検索が相当に高速化されます。以下にその例を示します。

テストデータの作成

ランダム作成した5～7文字の文字列データを十万件挿入したテーブルを作り(test_data)これを元データとします。さらにランダムに割り振られたグループで文字列をまとめてテストデータ(test_csv)を作ります。それなりにアイテム個数のバラけたランダムデータになってると思います。

元データとテストデータ
CREATE TABLE test_data AS 
  SELECT TRUNC(dbms_random.value * 10000) + 1                      n, 
         dbms_random.STRING('X', TRUNC(dbms_random.value * 3) + 5) val 
  FROM   dual 
  CONNECT BY LEVEL <= 100000; 

SELECT COUNT(*) FROM test_data; 

        COUNT(*)
----------------
         100,000


CREATE TABLE test_csv AS 
  SELECT n id, 
         LISTAGG(val, ',') within GROUP (ORDER BY NULL) items 
  FROM   test_data 
  GROUP  BY n; 

CREATE UNIQUE INDEX test_csv_u1 ON test_csv(id); 

SELECT COUNT(*), 
       MAX(items) keep (dense_rank first ORDER BY LENGTH(items)) mn, 
       MAX(items) keep (dense_rank last  ORDER BY LENGTH(items)) mx 
FROM   test_csv; 

        COUNT(*) MN                        MX
---------------- ------------------------- --------------------------------------------------------------------------------
          10,000 9WNRMH                    17UP1,1JCDC,2R5Y3K,3A8DBSU,3QEU9,3UH5VU,3Z30W9,3ZL3WJ,46LA8N,BO9241,F9PS61X,GJ2T
                                           CRJ,K2TOQZL,K9BWKWP,M0PCJJ2,MKQZAW,NDP1I,PY4CS,Q1I1PH3,T31A76,TK8NU4,TTXQE,ZLVY9
                                           5,ZYMII

10gではLISTAGGが使えないので以下のSQLを使います。

補足(10g)
CREATE TABLE test_csv AS 
  SELECT n                                          id, 
         SUBSTR (SYS_CONNECT_BY_PATH (val, ','), 2) items 
  FROM   (SELECT n, val, 
                 ROW_NUMBER() over (PARTITION BY n ORDER BY val) rn 
          FROM   test_data) 
  WHERE  connect_by_isleaf = 1 
  START WITH rn = 1 
  CONNECT BY PRIOR rn + 1 = rn AND PRIOR n = n;

部分一致検索テスト

10万個の元データをから抽出した5000件のアイテム値で、カンマ区切り文字列を検索してみます。たかだか5000件ですが、ネスト結合のフルスキャンを繰り返しているので55秒1もかかっています。さらに元データ全件をぶつけてみると15分ほどかかりました2。

LIKEでの部分一致検索
SELECT COUNT(id)
FROM   test_csv c,
       (SELECT val FROM test_data WHERE ROWNUM <= 5000) t
WHERE  c.items LIKE '%,' || t.val || ',%'
    or c.items like '%,' || t.val
    or c.items like         t.val || ',%'
    or c.items = t.val
;

Elapsed: 00:00:55.53

ちなみに、正規表現マッチ（REGEXP_LIKE）は高価なファンクションなので、大きなループ内で使用すると予想以上に時間がかかります。これはやめたほうがよいです。

REGEXP_LIKEでの部分一致検索
SELECT COUNT(id) 
FROM   test_csv c, 
       (SELECT val FROM test_data WHERE ROWNUM <= 5000) t 
WHERE  REGEXP_LIKE(c.items, '(^|,)' || t.val || '(,|$)'); 

Elapsed: 00:25:01.02

アイテム値の展開

カンマ区切りを展開する方法は色々ありますが、ここではなるべく高速な方法を選択して、補助テーブルとの結合のみでアイテム展開します。まず試しに一行だけでやってみます。

インラインビューで使用している階層サブクエリは行数を増やすための単なる補助テーブルであり、カンマ区切りの文字列に含まれるアイテム値の「最大個数」の行を作っています。例として200個としていますが、対象システムの定義次第です。補助テーブルの件数はパフォーマンスにはほとんど影響しないのでVARCHAR2の最大桁数である4000にしておいても問題ありません。

また、ヒントを追加していますが、これはオプティマイザがこの補助テーブルのカーディナリティを正しく計算できずに常に1としてしまうためです。ま、元テーブルがDUALですからね。その結果、ここで効率的となるソートマージ結合でなくネスト結合を選択してしまうため、手動でカーディナリティを設定して適切な結合の選択を促しています。そういう意味では補助テーブルは実テーブルのほうが分かりやすくてよいのかもしれません。

アイテム値展開
SELECT * FROM test_csv WHERE id = 1; 

        ID ITEMS
---------- ------------------------------------------------------------
         1 0CPB5,1BESALP,MBDXQ2K,PISRYZB,RHBOY,W6RUM6H,Y77A6T


SELECT rn,
       SUBSTR(items, 
           DECODE(rn, 1, 1, INSTR(items, ',', 1, rn - 1) + 1),    -- point of item start
           NVL(NULLIF(INSTR(items, ',', 1, rn), 0), 4000)         -- point of next comma
               - DECODE(rn, 1, 1, INSTR(items, ',', 1, rn - 1) + 1)
        ) item
FROM   (SELECT /*+ cardinality(200) */ LEVEL rn FROM dual CONNECT BY LEVEL <= 200) n,
       (SELECT items FROM test_csv WHERE id = 1) t
WHERE  LENGTH(items) - LENGTH(REPLACE(items, ',', '')) + 1 >= n.rn -- number of items
;

        RN ITEM
---------- --------------------
         1 0CPB5
         2 1BESALP
         3 MBDXQ2K
         4 PISRYZB
         5 RHBOY
         6 W6RUM6H
         7 Y77A6T

Elapsed: 00:00:00.01

上記の展開SQLを用いて全テストデータの文字列を展開してみます。小さな結合とメモリ上の計算だけなので、展開そのものには時間はほとんどかかりません。あとはデータの読み込み時間だけですが、テストデータテーブルが小さくかつキャッシュに乗っていることからほぼ瞬時に終わっています。

全テストデータの展開
with singles as (
SELECT id,
       SUBSTR(items,
           DECODE(rn, 1, 1, INSTR(items, ',', 1, rn - 1) + 1),
           NVL(NULLIF(INSTR(items, ',', 1, rn), 0), 4000)
               - DECODE(rn, 1, 1, INSTR(items, ',', 1, rn - 1) + 1)
    ) item
FROM   (SELECT /*+ cardinality(200) */ LEVEL rn FROM dual CONNECT BY LEVEL <= 200) n,
       test_csv t
WHERE  LENGTH(items) - LENGTH(REPLACE(items, ',', '')) + 1 >= n.rn
)
SELECT COUNT(*) FROM singles;

        COUNT(*)
----------------
         100,000

Elapsed: 00:00:00.06

では、展開したデータに対して5000件の検索をかけてみます。先程の部分一致検索では55秒ほどかかりましたが、アイテム展開処理込みで0.3秒です。さらに、元データ10万件すべてに対しても実施してみましたが、ハッシュ結合のため実行時間はほぼかわらず0.35秒で終了しました。部分一致検索で15分ほどかかっていた処理ですので、相当な高速化です。

また、アイテム展開することで、クエリのパフォーマンスだけではなく、フレキシビリティも向上しています。例えば、文字列前後のスペースを削除し忘れて登録しているグドグドなシステムであっても、またカンマ区切りリストの n番目アイテムのみを検索したいなどというよくわからない要求にも、問題なく対応できるようになります。

アイテム検索（5000件）
with singles as (
SELECT id,
       SUBSTR(items,
           DECODE(rn, 1, 1, INSTR(items, ',', 1, rn - 1) + 1),
           NVL(NULLIF(INSTR(items, ',', 1, rn), 0), 4000)
               - DECODE(rn, 1, 1, INSTR(items, ',', 1, rn - 1) + 1)
       ) item
FROM   (SELECT /*+ cardinality(200) */ LEVEL rn FROM dual CONNECT BY LEVEL <= 200) n,
        test_csv t
WHERE  LENGTH(items) - LENGTH(REPLACE(items, ',', '')) + 1 >= n.rn
)
SELECT COUNT(*) 
FROM   singles s, 
       (SELECT val FROM test_data WHERE ROWNUM <= 5000) t 
WHERE  s.item = t.val; 

        COUNT(*)
----------------
           5,000

Elapsed: 00:00:00.29

汎用ファンクション化

展開コードを何度もコピーするのは鬱陶しいので、適宜処理用に汎用ファンクションに落とし込んでみます。カンマ区切り文字列を与えると、展開したアイテム値をコレクションで返します。

追記：コレクションとテーブル表現について詳しくは以下を参照してください。
12cからレコード型のコレクションでもテーブル表現が使えるぞ

展開用ファンクション
CREATE OR replace TYPE items_tbl AS TABLE OF VARCHAR2(4000);
/

CREATE OR replace FUNCTION SEGREGATE(v VARCHAR2)
RETURN items_tbl
AS
  item_list items_tbl;
BEGIN
    SELECT CAST(COLLECT(
                    SUBSTR(items,
                           DECODE(rn, 1, 1, INSTR(items, ',', 1, rn - 1) + 1),
                           NVL(NULLIF(INSTR(items, ',', 1, rn), 0), 4000)
                               - DECODE(rn, 1, 1, INSTR(items, ',', 1, rn - 1) + 1)))
       AS items_tbl)
    INTO   item_list
    FROM   (SELECT LEVEL rn, v items
            FROM DUAL 
                 CONNECT BY LEVEL <= LENGTH(v) - LENGTH(REPLACE(v, ',', '')) + 1) n;

    RETURN item_list;
END;
/

SELECT id, 
       column_value item 
FROM   (SELECT id, items FROM test_csv WHERE id IN ( 1, 5 )), 
       TABLE(SEGREGATE(items)); 

        ID ITEM
---------- --------------------
         1 0CPB5
         1 1BESALP
         1 MBDXQ2K
         1 PISRYZB
         1 RHBOY
         1 W6RUM6H
         1 Y77A6T
         5 9CXYR
         5 FFSBL
         5 OHRNF6
         5 OZRYOQ9
         5 P7KF5W1

パフォーマンスを見るため、同様に展開と検索をしてみます。ファンクションコール繰返しのオーバーヘッドにより、展開部分で数十倍程度3の遅延が発生していますが、適宜処理には問題のないレベルでしょう。ファンクション自体は便利なので、パフォーマンスの許容できる範囲で使い分けるのがよいですね。

ファンクションを用いた展開と検索

SELECT count(id) FROM test_csv, TABLE(SEGREGATE(items));

Elapsed: 00:00:01.71


SELECT COUNT(*)
FROM   test_data t,
       (SELECT id, column_value item FROM test_csv, TABLE(SEGREGATE(items))) s
WHERE  s.item = t.val;

Elapsed: 00:00:01.84

削除及び追加への応用

さてせっかくなので、展開用ファンクションを応用して、カンマ区切り文字列からのアイテム値の削除、および並び替え挿入にも対応してみたいと思います。

これらの動作は、ファンクションで展開したアイテム値をLISTAGG で再集合化すればいいだけですので、非常に簡単です。LISTAGGが使えないオラクルのバージョンであれば、階層等を使います。

アイテム削除
SELECT v.id, 
       LISTAGG(t.column_value, ',') within GROUP (ORDER BY t.column_value) items 
FROM   (SELECT id, items FROM test_csv WHERE id IN ( 1, 5 )) v, 
       TABLE(SEGREGATE(v.items)) t 
WHERE  t.column_value NOT IN ( 'MBDXQ2K', 'OHRNF6', 'Y77A6T' ) 
GROUP  BY v.id;

        ID ITEMS
---------- --------------------------------------------------------------------------------
         1 0CPB5,1BESALP,PISRYZB,RHBOY,W6RUM6H
         5 9CXYR,FFSBL,OZRYOQ9,P7KF5W1
アイテム追加（並び替え）
SELECT v.id, 
       LISTAGG(t.column_value, ',') within GROUP (ORDER BY t.column_value) items 
FROM   (SELECT id, items FROM test_csv WHERE id IN ( 1, 5 )) v, 
       TABLE(SEGREGATE(v.items || ',A000000')) t 
GROUP  BY v.id; 

        ID ITEMS
---------- --------------------------------------------------------------------------------
         1 0CPB5,1BESALP,A000000,MBDXQ2K,PISRYZB,RHBOY,W6RUM6H,Y77A6T
         5 9CXYR,A000000,FFSBL,OHRNF6,OZRYOQ9,P7KF5W1

おわりに

現実ではここまでうまくいくものではありませんが、それでもネスト結合のフルスキャンを繰り返すくらいなら、開き直ってその場でデータ構造を変えてしまうくらいの大胆さがあったほうがパフォーマンスが向上するというお話でした。

ついでにカンマ区切りカラムでも削除や挿入がアプリからではなくSQLでも簡単に出来ることを示しましたが、Jaywalkingに問題がないといってるわけではないですからね。あくまでもWorkaroundです。

以上です。

  1. あくまで目安です。 ↩

  2. テーブル行サイズがが大きい場合は、TABLE FULL SCANの代わりにINDEX FAST FULL SCANして再結合する方法もありますが、気休めにしかならないでしょう。 ↩

  3. 10倍のデータ量(10万件)でテストしましたが同様の結果でした(0.5秒 vs 18秒)。 ↩

7
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
9