EXPLAINリクエスト修飾子およびMERGE条件ステップ - Advanced SQL Engine - Teradata Database

CREATE TABLE contact (
 contact_number INTEGER,
 contact_name   CHARACTER(30),
 area_code      SMALLINT NOT NULL,
 phone          INTEGER NOT NULL,
 extension      INTEGER )
UNIQUE PRIMARY INDEX (contact_number);

CREATE TABLE contact_t (
 number    INTEGER,
 name      CHARACTER(30),
 area_code SMALLINT NOT NULL,
 phone     INTEGER NOT NULL,
 extension INTEGER)
UNIQUE PRIMARY INDEX (number);

トリガーまたは結合インデックス ステップを伴わない単純な条件付きの挿入処理

次の例は、トリガー ステップまたは結合インデックス ステップを使用しない単純な条件挿入処理を示します。EXPLAINレポート内の該当句は太字で強調表示されています。

EXPLAIN 
MERGE INTO contact_t AS t
USING (SELECT contact_number, contact_name, area_code, phone, extension
       FROM contact
       WHERE contact_number = 8005) s
       ON (t.number =  8005)
       WHEN MATCHED THEN
       UPDATE SET name='Name beingUpdated',
                  extension = s.extension
       WHEN NOT MATCHED THEN
       INSERT (number, name, area_code, phone, extension)
       VALUES (s.contact_number, s.contact_name,
               s.area_code, s.phone, s.extension);

1) First, we do a single-AMP MERGE DELETE to TEST.contact_t from
   TEST.contact by way of a RowHash match scan. New updated rows are
   built and the result goes into Spool 1 (one-amp), which is built
   locally on the AMPs. Then we do a SORT to order Spool 1 by row
   hash.
2) Next, we execute the following steps in parallel.
     1) We do a single-AMP MERGE into TEST.contact_t from Spool 1
        (Last Use).
     2) If no update in 2.1, we do a single-AMP RETRIEVE step from
        TEST.contact by way of the unique primary index
        "TEST.contact.contact_number = 8005" with no residual
        conditions into Spool 2 (one-amp), which is built locally on
        that AMP. Then we do a SORT to order Spool 2 by row hash.
        The size of Spool 2 is estimated with high confidence to be 1
          row. The estimated time for this step is 0.02 seconds.
3) If no update in 2.1, we do a single-AMP MERGE into TEST.contact_t
   from Spool 2 (Last Use).
...

このEXPLAINは<step number>条件ステップで更新が発生しなかった場合、つまりcontact_tへの更新が失敗した場合にのみ実行されるステップを示しています。レポートでは、MERGEが最初に更新操作の実行を試行することを示しています(ステップ2.1)。更新する行がない場合は、文により新しい行が挿入されます(ステップ3)。

トリガー ステップを伴わない比較的複雑な条件付きの挿入処理

次の例は、少し複雑な条件挿入処理を示しています。これは、テーブルt1およびt2に結合インデックスが定義されている場合です。

結合インデックスのDDLは次のとおりです。

CREATE JOIN INDEX j AS
 SELECT * FROM t1 LEFT OUTER JOIN t2
 ON (t1.y1 = t2.y2);

EXPLAINレポート内の該当句は太字で強調表示されています。

EXPLAIN 
MERGE INTO t1
USING VALUES(1,2) AS s(x1, y1)
ON t1.x1 = 4
WHEN MATCHED THEN
UPDATE SET y1 = 5
WHEN NOT MATCHED THENINSERT(4,5);

...
3) We execute the following steps in parallel.
     1) We do a single-AMP DELETE from TEST.j by way of the primary
        index "TEST.j.x1 = 4" with no residual conditions.
     2) We do an all-AMPs RETRIEVE step from TEST.t2 by way of an
        all-rows scan with a condition of ("TEST.t2.y2 = 5") into
        Spool 2 (one-amp), which is redistributed by hash code to all
        AMPs. The size of Spool 2 is estimated with no confidence to
        be 1 row. The estimated time for this step is 0.02 seconds.
4) We do a single-AMP JOIN step from TEST.t1 by way of the primary
   index "TEST.t1.x1 = 4" with no residual conditions, which is
   joined to Spool 2 (Last Use). TEST.t1 and Spool 2 are left outer
   joined using a product join, with a join condition of ("(1=1)").
   The result goes into Spool 1 (one-amp), which is redistributed by
   hash code to all AMPs. Then we do a SORT to order Spool 1 by row
   hash. The size of Spool 1 is estimated with no confidence to be 3
   rows. The estimated time for this step is 0.03 seconds.
5) We execute the following steps in parallel.
     1) We do a single-AMP MERGE into TEST.j from Spool 1 (Last Use).
     2) We do a single-AMP UPDATE from TEST.t1 by way of the primary
        index "TEST.t1.x1 = 4" with no residual conditions.
     3) If no update in 5.2, we do an INSERT into TEST.t1.
     4) If no update in 5.2, we do an INSERT into Spool 3.
     5) If no update in 5.2, we do an all-AMPs RETRIEVE step from
        TEST.t2 by way of an all-rows scan with no residual
        conditions into Spool 5 (all_amps), which is duplicated on
        all AMPs. The size of Spool 5 is estimated with low
        confidence to be 4 rows. The estimated time for this step is
        0.02 seconds.
6) If no update in 5.2, we do an all-AMPs JOIN step from Spool 3
   (Last Use) by way of an all-rows scan, which is joined to Spool 5
   (Last Use). Spool 3 and Spool 5 are left outer joined using a
   product join, with a join condition of ("y1 = y2"). The result
   goes into Spool 4 (one-amp), which is redistributed by hash code
   to all AMPs. Then we do a SORT to order Spool 4 by row hash. The
   size of Spool 4 is estimated with no confidence to be 2 rows. The
   estimated time for this step is 0.03 seconds.
7) If no update in 5.2, we do a single-AMP MERGE into TEST.j from
   Spool 4 (Last Use).
...

ここでも、<step number>条件ステップに更新がない場合の処理がEXPLAINでレポートされています。これらのステップは、テーブルt1への最初の無条件更新が成功しなかった場合に限り実行されます。レポートでは、MERGEが最初に更新操作の実行を試行することを示しています(ステップ5.2)。更新する行がない場合は、新しい行が挿入されます(ステップ5.3および5.4)。結合インデックスjは、ステップ3.1、5.1および7で更新されています。

Upsertトリガーを伴う条件付きの挿入処理

次の例では、Upsertトリガーがテーブルt1に定義されている場合の条件挿入処理を示します。

トリガーのDDLは次の通りです。

CREATE TRIGGER r1 AFTER INSERT ON t1
(UPDATE t2 SET y2 = 9 WHERE x2 = 8
ELSE INSERT t2(8,9););

EXPLAINレポート内の該当句は太字で強調表示されています。

EXPLAIN 
MERGE INTO t1
USING VALUES(1,2) AS s(x1, y1)
ON t1.x1 = 4
WHEN MATCHED THEN
UPDATE SET y1 = 5
WHEN NOT MATCHED THEN
INSERT(4,5);

1) First, we execute the following steps in parallel.
     1) We do a single-AMP UPDATE from TEST.t1 by way of the primary
        index "TEST.t1.x1 = 4" with no residual conditions.
     2) If no update in 1.1, we do an INSERT into TEST.t1.
     3) If no update in 1.1, we do a single-AMP UPDATE from TEST.t2
        by way of the primary index "TEST.t2.x2 = 8" with no residual
        conditions. If the row cannot be found, then we do an INSERT
        into TEST.t2.
...

テーブルt1に対する無条件更新はステップ1.1で試行されます。更新が成功しなかった場合は、MERGEがステップ1.2でt1に新しい行を挿入し、トリガーr1を起動します。これによりステップ1.3でテーブルt2への更新が試行されます。更新を実行することができない場合、トリガーはテーブルt2に新しい行を挿入します。

アボート トリガーを伴う条件付きの挿入処理

次の例では、アボート トリガーがテーブルt4への更新後に起動されるよう定義されている場合の条件挿入処理を示します。

この例では、このDDLで定義されている条件アボート トリガーを使用します。

CREATE TRIGGER aborttrig AFTER UPDATE ON t4
(UPDATE t5 SET y5 =5 WHERE x5 = 3
ELSE INSERT t5(3,5);
ABORT FROM t5 WHERE x5 =1;
DELETE t3 WHERE x3 = 10;
ABORT 'unconditional abort';);

EXPLAINレポート内の該当句は太字で強調表示されています。

EXPLAIN 
MERGE INTO t4
USING VALUES(1,2) AS s(x1, y1)
ON t4.x4 = 4
WHEN MATCHED THEN
UPDATE SET y4 = 5
WHEN NOT MATCHED THEN
INSERT(4,5);

1) First, we execute the following steps in parallel.
     1) We do a single-AMP UPDATE from TEST.t4 by way of the primary
        index "TEST.t4.x4 = 4" with no residual conditions.
     2) We do a single-AMP UPDATE from TEST.t5 by way of the primary
        index "TEST.t5.x5 = 3" with no residual conditions. If the
          row cannot be found, then we do an INSERT into TEST.t5.
2) Next, we execute the following steps in parallel.
     1) If no update in 1.1, we do a single-AMP ABORT test from
        TEST.t5 by way of the primary index "TEST.t5.x5 = 1" with no
        residual conditions.
     2) If no update in 1.1, we do a single-AMP DELETE from TEST.t3
        by way of the primary index "TEST.t3.x3 = 10" with no
          residual conditions.
3) If no update in 1.1, we unconditionally ABORT the transaction.
4) If no update in 1.1, we do an INSERT into TEST.t4.
...

t4テーブルに対する無条件更新はステップ1.1で試行されます。更新が成功しない場合、トリガー アボートが起動され、ステップ1.2でテーブルt5においてAtomic Upsertの実行が試行されます。

テーブルt4が更新されていない場合は、ステップ1.2でMERGEによりテーブルt4に新しい行が挿入され、トリガー アボートが起動され、ステップ1.3でテーブルt2に対するAtomic Upsert操作の更新の実行が試行されます。更新を実行することができない場合、トリガーはテーブルt2に新しい行を挿入します。

これらの例でのEXPLAINリクエスト修飾子の用語

EXPLAINレポートのこのセットの新しい用語は、次のように定義されます。