15.10 - EXPLAIN Request Modifier and MERGE Conditional Steps - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

The EXPLAIN request modifier is useful in determining the conditional steps in MERGE processing. MERGE conditional steps are insert operations that are performed after an unconditional update operation does not meet its matching condition only when both WHEN MATCHED and WHEN NOT MATCHED clauses are specified.

The following DDL statements create the tables accessed by the EXPLAINed DML statement examples:

     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); 

The following example demonstrates simple conditional insert processing without trigger or join index steps. The relevant phrases in the EXPLAIN report are highlighted in boldface type:

     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) ;
 
     *** Help information returned. 20 rows.
    *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  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).
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

Only if no update in <step number> conditional steps are reported by this EXPLAIN, indicating the steps to be performed only if the update to contact_t fails. The report indicates that the MERGE first attempts to perform an update operation (step 2.1). If no row is found to update, then the statement inserts a new row (step 3).

The following example demonstrates slightly more complicated conditional insert processing when a join index is defined on tables t1 and t2.

The DDL for the join index is as follows.

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

The relevant phrases in the EXPLAIN report are highlighted in boldface type:

     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);
 
    *** Help information returned. 44 rows.
    *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct TEST."pseudo table" for read on a
     RowHash to prevent global deadlock for TEST.t2.
  2) Next, we lock TEST.t2 for read.
  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).
  8) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

Again, only if no update in <step number> conditional steps are reported by this EXPLAIN. These steps are performed only if the initial unconditional update attempt to table t1 is unsuccessful. The report indicates that the MERGE first attempts to perform an update operation (step 5.2). If no row is found to update, then the statement inserts a new row (steps 5.3 and 5.4). The join index j is updated by steps 3.1, 5.1, and 7.

The following example demonstrates conditional insert processing when an upsert trigger is defined on table t1.

The DDL for the trigger is as follows:

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

The relevant phrases in the EXPLAIN report are highlighted in boldface type:

     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);
 
    *** Help information returned. 11 rows.
    *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  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.
  2) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

The unconditional update to table t1 is attempted in step 1.1. If the update is unsuccessful, then the MERGE inserts a new row into t1 in step 1.2 and fires trigger r1, which attempts to update table t2 in step 1.3. If an update cannot be performed, then the trigger inserts a new row into table t2.

The following example demonstrates conditional insert processing when an abort trigger is defined to fire after an update on table t4.

This example uses the conditional abort trigger defined by this 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';);

The relevant phrases in the EXPLAIN report are highlighted in boldface type:

     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);
    
    *** Help information returned. 18 rows.
    *** Total elapsed time was 1 second.
   
Explanation
---------------------------------------------------------------------------
  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.
  5) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

The unconditional update to table t4 is attempted in step 1.1. If the update is successful, then trigger aborttrig is fired, which attempts to perform an atomic upsert on table t5 in step 1.2.

If no update is made to table t4, then the MERGE inserts a new row into it in step 1.2 and fires trigger aborttrig, which attempts to perform an atomic upsert operation update on table t2 in step 1.3. If an update cannot be performed, then the trigger inserts a new row into table t2.

New terminology in this set of EXPLAIN reports is defined as follows:

 

Phrase

Definition

if no update in <step number>

The match condition for the update phase was not met, so the conditional insert phase will be performed.

<step number> indicates the MERGE update step.

if <step number> not executed,

Indicates the action taken if the first step in an UPDATE block in not performed.

Reported only if <step number> is greater than 1.