階層データの選択

クエリの記述方法によって、再帰レベル数を制限できます。たとえば、レベル数を制限して、トップレベルの管理者のみを返すことができますが、指揮系統が予期したよりも長い場合、除外される従業員も出てきます。レベル数を制限しない場合は、従業員が除外されることはありません。ただし、ある従業員が直接または間接的に自分自身に対してレポートするなど、実行に循環が必要な場合は、無限の再帰が発生する可能性があります。この状況は、たとえば会社の従業員が取締役会の一員である場合などに、社内の管理階層内で発生することがあります。

次のクエリは、管理レベルで従業員をリストする方法を示しています。level 0 は、管理者を持たない従業員を表します。level 1 は、level 0 の管理職の 1 人に直接報告を行う従業員を表し、level 2 は、level 1 の管理職に直接報告を行う従業員を表します。以下、同様に続きます。

WITH RECURSIVE
  manager ( EmployeeID, ManagerID,
            GivenName, Surname, mgmt_level ) AS
( ( SELECT EmployeeID, ManagerID,       -- initial subquery
           GivenName, Surname, 0
    FROM Employees AS e
    WHERE ManagerID = EmployeeID )
  UNION ALL
  ( SELECT e.EmployeeID, e.ManagerID,   -- recursive subquery
           e.GivenName, e.Surname, m.mgmt_level + 1
    FROM Employees AS e JOIN manager AS m
     ON   e.ManagerID =  m.EmployeeID
      AND e.ManagerID <> e.EmployeeID
      AND m.mgmt_level < 20 ) )
SELECT * FROM manager
ORDER BY mgmt_level, Surname, GivenName;

再帰クエリ内の条件として管理レベルを 20 未満に制限するのは、重要な予防措置です。これによって、テーブル・データに循環が含まれる場合も無限再帰を防げます。

max_recursive_iterations オプション

max_recursive_iterations オプションは、暴走する再帰クエリを捕獲することを目的として設計されています。このオプションのデフォルト値は 100 です。この再帰レベル数を超えた再帰クエリは終了しますが、エラーを発生させます。

このオプションがあれば停止条件は重要でないように見えるかもしれませんが、通常はそうではありません。各繰り返しの間に選択されるローの数は、急激に増える可能性があり、最大に達する前にデータベースのパフォーマンスに深刻な影響を与えることがあります。再帰クエリ内に停止条件を設けるのは、各状況に適した制限を設定する手段です。