階層クエリ

階層クエリ（Hierarchical Query）とは、特殊な機能を持つクエリステートメントであり、階層データを階層関係に従って表示することができます。

階層データとは、リレーショナルテーブル内のデータ間に階層関係が存在することを指します。このような関係は現実世界でも非常に一般的であり、例として以下のようなものが挙げられます：

• 組織構造におけるグループリーダーとグループメンバーの関係

• 企業における上級部門と下級部門の関係

• Webページにおけるページ遷移の関係

制限事項と注意点

階層クエリにFOR UPDATE句が含まれている場合、以下の使用シナリオはサポートされません：

• サブクエリでDISTINCTキーワードまたは集計関数が使用されている場合、この構文はFOR UPDATEと一緒に使用できません。
• 共通テーブル式（Common Table Expressions、CTE）を含むシナリオはすべてサポートされていません。つまり、WITH ... AS ...句を含むSELECTクエリはFOR UPDATEと同時に使用できません。WITH ... AS ...句の詳細については、WITH CLAUSEを参照してください。

データベースの正常な動作とパフォーマンスの最適化を確保するため、上記の2つのシナリオでFOR UPDATE句を使用することは避けてください。

構文

単純な階層クエリのSELECT構文は次のとおりです：

SELECT [LEVEL], column, expr... FROM table [WHERE condition]
[ START WITH start_expression ] CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr } [ ORDER SIBLINGS BY ...]
[ GROUP BY ... ] [ HAVING ... ] [ ORDER BY ... ]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]

SELECT構文の詳細については、SIMPLE SELECTを参照してください。

パラメータの説明

実行プロセス

階層クエリを使用し実装する上で最も重要なのは、その実行プロセスを理解することです。一般的な階層クエリの実行プロセスは以下の通りです：

1. FROM の後に続く SCAN または JOIN 操作を実行します。

2. START WITH および CONNECT BY の内容に基づいて階層関係の結果を生成します。

   ステップ2で生成される階層関係のプロセスは、以下のように理解できます：

   1. START WITH 内の式に基づいてルート行（Root Rows）を取得します。

   2. CONNECT BY 内の式に基づいて、各ルート行（Root Rows）の子行（Child Rows）を選択します。

3. ステップ2で生成された子行（Child Rows）を新たなルート行（Root Rows）として、さらに子行（Child Rows）を生成し、新しい行が生成されなくなるまで繰り返し実行します。

4. 残りの句（例えば WHERE、GROUP、ORDER BY など）は通常のクエリ実行プロセスに従って実行します。

例

1. テーブル emp を作成し、テーブル内の emp_id、position、mgr_id 列にデータを挿入します。

   CREATE TABLE emp(emp_id INT,position VARCHAR(50),mgr_id INT);
   INSERT INTO emp VALUES (1,'グローバルマネージャー',NULL);
   INSERT INTO emp VALUES (2,'ヨーロッパマネージャー',1);
   INSERT INTO emp VALUES (3,'アジア太平洋マネージャー',1);
   INSERT INTO emp VALUES (4,'アメリカマネージャー',1);
   INSERT INTO emp VALUES (5,'イタリアマネージャー',2);
   INSERT INTO emp VALUES (6,'フランスマネージャー',2);
   INSERT INTO emp VALUES (7,'中国マネージャー',3);
   INSERT INTO emp VALUES (8,'韓国マネージャー',3);
   INSERT INTO emp VALUES (9,'日本マネージャー',3);
   INSERT INTO emp VALUES (10,'アメリカマネージャー',4);
   INSERT INTO emp VALUES (11,'カナダマネージャー',4);
   INSERT INTO emp VALUES (12,'北京マネージャー',7);
   

   上記のサンプルテーブルの列 position は明確な階層関係を持っています。ツリー構造は以下のとおりです：

2. 以下のステートメントを実行して、結果を階層構造で表示します。

   SELECT emp_id, mgr_id, position, level
   FROM emp
   START WITH mgr_id IS NULL
       CONNECT BY PRIOR emp_id = mgr_id;
   

   実行結果は次のとおりです：

   +--------+--------+--------------------+-------+
   | EMP_ID | MGR_ID | POSITION           | LEVEL |
   +--------+--------+--------------------+-------+
   |      1 |   NULL | グローバルマネージャー           |     1 |
   |      2 |      1 | ヨーロッパマネージャー         |     2 |
   |      5 |      2 | イタリアマネージャー       |     3 |
   |      6 |      2 | フランスマネージャー         |     3 |
   |      3 |      1 | アジア太平洋マネージャー         |     2 |
   |      7 |      3 | 中国マネージャー         |     3 |
   |     12 |      7 | 北京マネージャー         |     4 |
   |      8 |      3 | 韓国マネージャー         |     3 |
   |      9 |      3 | 日本マネージャー         |     3 |
   |      4 |      1 | アメリカマネージャー         |     2 |
   |     10 |      4 | アメリカマネージャー         |     3 |
   |     11 |      4 | カナダマネージャー       |     3 |
   +--------+--------+--------------------+-------+
   12 rows in set
   

3. "アジア太平洋" の階層構造のみを照会し、FOR UPDATE 句を使用してクエリ結果をロックします。

   SELECT emp_id, mgr_id, position, LEVEL
   FROM emp
   START WITH position = 'アジア太平洋マネージャー'
       CONNECT BY PRIOR emp_id = mgr_id
   FOR UPDATE;
   

   実行結果は次のとおりです：

   +--------+--------+-----------------+-------+
   | EMP_ID | MGR_ID | POSITION        | LEVEL |
   +--------+--------+-----------------+-------+
   |      3 |      1 | アジア太平洋マネージャー      |     1 |
   |      7 |      3 | 中国マネージャー      |     2 |
   |     12 |      7 | 北京マネージャー      |     3 |
   |      8 |      3 | 韓国マネージャー      |     2 |
   |      9 |      3 | 日本マネージャー      |     2 |
   +--------+--------+-----------------+-------+
   5 rows in set
   

関連ドキュメント

• SIMPLE SELECT
• WITH句
• クエリ結果のロックSELECT FOR UPDATE