17.00 - 17.05 - EXPLAINリクエスト修飾子と再帰 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

再帰的SELECTの例

次の例では再帰的問合わせを示しています。

EXPLAIN 
WITH RECURSIVE temp_table (employee_number, depth) AS
(SELECT root.employee_number, 0 as depth
 FROM Employee root
 WHERE root.manager_employee_number = 801
 UNION ALL
  SELECT indirect.employee_number, seed.depth+1 as depth
  FROM temp_table seed, Employee indirect
  WHERE seed.employee_number = indirect.manager_employee_number
  AND depth <= 20)
SELECT employee_number, depth FROM temp_table;

EXPLAINではこのリクエストに対して次の応答を生成します。

...
2) Next, we lock PERSONNEL.root for read.
3) We do an all-AMPs RETRIEVE step from PERSONNEL.root by
   way of an all-rows scan with a condition of (
   "PERSONNEL.root.manager_employee_number = 801") into
   Spool 3 (all_amps), which is built locally on the AMPs. The size
   of Spool 3 is estimated with no confidence to be 1 row. The
   estimated time for this step is 0.06 seconds.
4) We do an all-AMPs RETRIEVE step from Spool 3 by way of an all-rows
   scan into Spool 2 (all_amps), which is built locally on the AMPs.
   The size of Spool 2 is estimated with no confidence to be 1 row.
   The estimated time for this step is 0.07 seconds.
5) We execute the following steps in parallel.
     1) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by
        way of an all-rows scan with a condition of ("(DEPTH <= 20)
        AND (NOT (EMPLOYEE_NUMBER IS NULL ))") into Spool 4
        (all_amps), which is duplicated on all AMPs. The size of
        Spool 4 is estimated with no confidence to be 8 rows. The
        estimated time for this step is 0.03 seconds.
     2) We do an all-AMPs RETRIEVE step from
        PERSONNEL.indirect by way of an all-rows scan with a
        condition of
        ("NOT (PERSONNEL.indirect.manager_employee_number IS NULL)")
        into Spool 5 (all_amps), which is built locally on the AMPs.
        The size of Spool 5 is estimated with no confidence to be 8
        rows. The estimated time for this step is 0.01 seconds.
6) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
   all-rows scan, which is joined to Spool 5 (Last Use) by way of an
   all-rows scan. Spool 4 and Spool 5 are joined using a single
   partition hash join, with a join condition of ("EMPLOYEE_NUMBER =
   manager_employee_number"). The result goes into Spool 6
   (all_amps), which is built locally on the AMPs. The size of Spool
   6 is estimated with no confidence to be 3 rows. The estimated
   time for this step is 0.08 seconds.
7) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by way of
   an all-rows scan into Spool 3 (all_amps), which is built locally
   on the AMPs. The size of Spool 3 is estimated with no confidence
   to be 4 rows. The estimated time for this step is 0.07 seconds.
   If one or more rows are inserted into spool 3, then go to step 4.
8) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
   an all-rows scan into Spool 7 (all_amps), which is built locally
   on the AMPs. The size of Spool 7 is estimated with no confidence
   to be 142 rows. The estimated time for this step is 0.07 seconds.
...

ステップ3は再帰問合わせ内のシード文の処理を指定し、最初の一時的な結果セットを作成します。

ステップ4~7は、再帰問合わせ内の再帰的文の処理と一時結果セットに挿入する新しい行がなくなるまでの繰り返しに相当します。ステップ4~7は静的計画を示しますが、各繰り返しではさまざまなカーディナリティを持つスプールが生成される可能性があります。このため、これらのステップではスプール サイズの信頼度のレベルは信頼度なしに設定されます。

ステップ8ではユーザーに送信される最終結果の処理を指します。