【Oracle DB】階層問い合わせでジェイクウォーク（信号無視）に立ち向かう

ジェイクウォーク（信号無視）

ジェイクウォークとはSQLアンチパターン（「あるある」だけど基本的には避けるべきパターン）の一つです。
一例としては、一つのカラムにカンマ区切りで複数のデータを持たせるようなデータ構造を指します。RDBを使用しているのであれば、テーブルを分割する設計が妥当です。

SQLアンチパターンをご存知の方であれば、このようなテーブルは特殊な事情がない限り作らないと思いますが、既存のシステム開発案件に後から合流し、このようなテーブルが既に存在していた場合はどうしようもありません。
その場合もまずは第一にテーブル設計の見直しを考えたいところですが、問題を抱えているテーブルに対し多数のプログラムが参照している状況だと容易にテーブル定義を変えることはできません。

本記事ではそのようなシチュエーションに対して、Oracle DBの階層問い合わせを使用することで立ち向かう例を紹介します。

使用するサンプルデータ

今回は以下のようなサンプルデータを用意してみます。指定したアイドルユニットに所属するメンバーを抽出することが今回のゴールです。
ポイントはいかにしてカンマ区切りのデータを縦持ちさせるか、になります。

アイドルユニットテーブル（idol_units）

unit_name	member_list
ザ・ドリフターズ	1,2,4,12,13,15
こぶ茶バンド	4,13,15
SMAP	3,5,6,11,14,16

タレントテーブル（talents)

id	name
1	荒井 注
2	いかりや 長介
3	稲垣 吾郎
4	加藤 茶
5	香取 慎吾
6	木村 拓哉
11	草彅 剛
12	志村 けん
13	高木 ブー
14	中居 正広
15	仲本 工事
16	森 且行

LIKE演算子での検索ができないよう、意図的に桁の繰上げをしています。

DDLとデータ投入用INSERT文

-- アイドルユニットテーブル作成＋データ投入
CREATE TABLE idol_units(unit_name VARCHAR2(30 CHAR) PRIMARY KEY, member_list VARCHAR2(50 CHAR) NOT NULL);
INSERT INTO idol_units VALUES('ザ・ドリフターズ', '1,2,4,12,13,15');
INSERT INTO idol_units VALUES('こぶ茶バンド', '4,13,15');
INSERT INTO idol_units VALUES('SMAP', '3,5,6,11,14,16');
--
-- タレントテーブル作成＋データ投入
CREATE TABLE talents(id NUMBER(3, 0) PRIMARY KEY, name VARCHAR2(30 CHAR) NOT NULL);
INSERT INTO talents VALUES(1, '荒井 注');
INSERT INTO talents VALUES(2, 'いかりや 長介');
INSERT INTO talents VALUES(3, '稲垣 吾郎');
INSERT INTO talents VALUES(4, '加藤 茶');
INSERT INTO talents VALUES(5, '香取 慎吾');
INSERT INTO talents VALUES(6, '木村 拓哉');
INSERT INTO talents VALUES(11, '草彅 剛');
INSERT INTO talents VALUES(12, '志村 けん');
INSERT INTO talents VALUES(13, '高木 ブー');
INSERT INTO talents VALUES(14, '中居 正広');
INSERT INTO talents VALUES(15, '仲本 工事');
INSERT INTO talents VALUES(16, '森 且行');
--
COMMIT;

階層問い合わせでデータを取り出す

データを抽出するクエリは以下となります。
ポイントはIN句の中のサブクエリ。カンマで区切られた要素の数分、階層問い合わせを繰り返します。

SELECT
    t1.name
FROM
    talents t1
WHERE 1=1
AND t1.id IN (
    SELECT
        REGEXP_SUBSTR(t2.member_list, '[^,]+', 1, LEVEL)
    FROM
        idol_units t2
    START WITH t2.unit_name = 'ザ・ドリフターズ'
    CONNECT BY REGEXP_COUNT(t2.member_list, '[^,]+') >= LEVEL
    AND t2.unit_name = 'ザ・ドリフターズ'
)
ORDER BY
    t1.id
;

クエリの結果は以下のとおりです。

NAME                          
------------------------------
荒井 注
いかりや 長介
加藤 茶
志村 けん
高木 ブー
仲本 工事

サブクエリ内のリテラル'ザ・ドリフターズ'が冗長なように見えますが、親子関係を示すキーを持たない構造のテーブルに対して再帰的に問い合わせを行う都合上、START WITH句とCONNECT BY条件の両方で指定しないと、検索結果に重複が生じてしまいます。
（IN句内のサブクエリで重複するだけなので、クエリ全体としては期待通りの結果は得られます）
外部サイトで紹介されている例ではDISTINCT句を用いて重複除去する例が散見されましたが、より低コスト（パフォーマンスに優れる）なクエリを実行したい場合は、たとえ冗長に見えても本例のように実装することが望ましいでしょう。
階層問い合わせにおける評価順の詳細は、旧バージョンのドキュメントですが以下が参考になります。

別解

階層問い合わせではWHERE句などの論理演算が行われるタイミングを意識する必要があるため、テーブル内にある任意の1レコードを再帰的に使用したい場合は、サブクエリをネストしてあらかじめ1レコードに絞り込んだ、以下のクエリで実装するとシンプルになる可能性があります。
ネストが深くなるため、忌避感がある方もいるかと思いますが、必要に応じて適宜使い分けても良いでしょう。

SELECT
    t1.name
FROM
    talents t1
WHERE 1=1
AND t1.id IN (
    SELECT
        REGEXP_SUBSTR(t3.member_list, '[^,]+', 1, LEVEL)
    FROM
    (
        SELECT
            t2.member_list
        FROM
            idol_units t2
        WHERE 1=1
        AND t2.unit_name = 'ザ・ドリフターズ'
    ) t3
    CONNECT BY REGEXP_COUNT(t3.member_list, '[^,]+') >= LEVEL
)
ORDER BY
    t1.id
;

終わりに

いくつかのプロジェクトを練り歩いていると、この手段を忘れかけた頃にジェイクウォークが現れる、といったことを何度か経験しました。
本記事が未来の私自身と、今まさにジェイクウォークに遭遇したあなたのお役に立てれば幸いです。

参考文献