無限再帰処理を回避するための深さ制御 - Advanced SQL Engine - Teradata Database

データ階層が循環している場合、または再帰的問合わせに指定された結合条件が適切でない場合、有限の結果を出して終了することが決してないリクエストとなって暴走することがあります。

以下の文の再帰的問合わせには、不正確な結合条件が指定されています。結合条件WHERE indirect.employee_number IN (1003, 1004)は、結果が常に真となるため、正確ではありません。

     WITH RECURSIVE temp_table (employee_id, level) AS (
       SELECT root.employee_number, 0 AS level       
       FROM employee AS root
       WHERE root.employee_number = 1003
     UNION ALL
       SELECT direct.employee_id, direct.level + 1  /* <--recursive statement*/ 
       FROM temp_table AS direct, employee AS indirect
       WHERE indirect.employee_number IN (1003,1004)
       )
     SELECT *
     FROM temp_table
     ORDER BY level;

この問合わせの結果セットは次のとおりです。

  employee_id  level
  -----------  -----
         1003      0
         1003      1
         1003      1
         1003      2
         1003      2
         1003      2
         1003      2
         1003      3
         1003      3
         1003      3
         1003      3
         1003      3
         1003      3
         1003      3
         1003      3
          ...    ...

このように無限に続きます。

次の例では、再帰のレベル数を制限するために、前のクエリーのWITH修飾子の再帰的な名前付きクエリーに結合条件(AND direct.level < 2)を追加します。

     WITH RECURSIVE temp_table (employee_id, level) AS (
       SELECT root.employee_number, 0 AS level    
       FROM employee AS root
       WHERE root.employee_number = 1003
     UNION ALL
       SELECT direct.employee_id, direct.level+1  
       FROM temp_table AS direct, employee AS indir
       WHERE indir.employee_number IN (1003,1004) 
       AND   direct.level < 2                     
       )
     SELECT *
     FROM temp_table
     ORDER BY level;

深さ制御列の初期値に指定する数値リテラルのデータ型は、その値を入れることのできる最小のデータ型です。前述のクエリーで、数値リテラル0のデータ型は、値0を入れることのできる最小の型であるBYTEINTです。

深さ制御列の初期値のデータ型は、再帰レベル数を、そのデータ型で表現可能な最大値に制限する働きをします。

例えば、BYTEINTの最大値は127です。127レベルを超える再帰処理が必要な場合は、深さ制御列の初期値に指定する数値リテラルを、もっと大きい型にCASTする必要があります。