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

Teradata Vantage™ - SQLデータ操作言語

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1146-171K-JPN
Language
日本語 (日本)

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

この文脈では、不適切な結合は、これらのエラーを1つ以上含む結合として定義されます。
  • 不正確な列を結合する
  • 結合から誤った列を選択する
  • AND演算子でなくOR演算子を複数の結合条件に指定する
  • 常にtrueとなる結合条件を指定する

以下の文の再帰的問合わせには、不正確な結合条件が指定されています。結合条件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
          ...    ...

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

以下のようにして、再帰の深さを制御するのが最善です。
  • 再帰的な名前付きクエリーの列リストで、深さ制御の列を指定します。
  • シード文の中で、その列の値を0に初期設定する。
  • 再帰文では、その列の値に1を加算する。
  • 再帰文の結合条件に、深さ制御用の列の限界値を指定する。

次の例では、再帰のレベル数を制限するために、前のクエリーの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する必要があります。