EXPLAIN Request Modifier and Triggers | Interpreting EXPLAIN Output | Vantage - EXPLAIN Request Modifier and Triggers - Advanced SQL Engine - Teradata Database

Row Trigger with Looping Trigger Action Example

In this example three tables t1, t2 and t3, and an AFTER row trigger with t1 as the subject table, are created. The trigger action modifies tables t2 and t3.

The EXPLAIN text for the INSERT operation, which is part of the trigger action, specifically marks the beginning and ending of the row trigger loop. The relevant phrases in the EXPLAIN report are highlighted in boldface type:

The DDL statements for creating the tables and the trigger are as follows:

CREATE TABLE t1(
 i INTEGER,
 j INTEGER);

CREATE TABLE t2(
 i INTEGER,
 j INTEGER);

CREATE TABLE t3(
 i INTEGER,
 j INTEGER);

CREATE TRIGGER g1 AFTER INSERT ON t1
FOR EACH ROW (
 UPDATE t2 SET j = j+1;
 DELETE t2;
 DELETE t3;);

The EXPLAIN text reports the steps used to process the following INSERT ... SELECT statement:

EXPLAIN 
INSERT t1 SELECT * FROM t3;

1) First, we lock mws.t3 in TD_MAP1 for write on a reserved RowHash
   to prevent global deadlock.
2) Next, we lock mws.t2 in TD_MAP1 for write on a reserved RowHash
   to prevent global deadlock.
3) We lock mws.t1 in TD_MAP1 for write on a reserved RowHash to
   prevent global deadlock.
4) We lock mws.t3 in TD_MAP1 for write, we lock mws.t2 in TD_MAP1
   for write, and we lock mws.t1 in TD_MAP1 for write.
5) We do an all-AMPs MERGE step in TD_MAP1 into mws.t1 from mws.t3
   followed by an insert in Spool 1.  The size is estimated with low
   confidence to be 8 rows.  The estimated time for this step is 3.94
   seconds.
6) <BEGIN ROW TRIGGER LOOP>

   we do an all-AMPs UPDATE step in TD_MAP1 from mws.t2 by way of an
   all-rows scan with no residual conditions.  The size is estimated
   with low confidence to be 8 rows.  The estimated time for this
   step is 0.09 seconds.
7) We do an all-AMPs DELETE step in TD_MAP1 from mws.t2 by way of an
   all-rows scan with no residual conditions.  The size is estimated
   with low confidence to be 8 rows.  The estimated time for this
   step is 2.44 seconds.
8) We do an all-AMPs DELETE step in TD_MAP1 from mws.t3 by way of an
   all-rows scan with no residual conditions.  The size is estimated
   with low confidence to be 8 rows.  The estimated time for this
   step is 2.44 seconds.
   <END ROW TRIGGER LOOP> for step 6.
9) We spoil the parser's dictionary cache for the table.
10) We spoil the parser's dictionary cache for the table.
11) We spoil the parser's dictionary cache for the table.
...