Conditional Steps | Interpreting EXPLAIN Output | Teradata Vantage - 17.10 - EXPLAIN Request Modifier and MERGE Conditional Steps - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

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.

Database Object DDL for Examples

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

Simple Conditional Insert Processing Without Trigger or Join Index Steps

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

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

More Complex Conditional Insert Processing Without Trigger Steps

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

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.

Conditional Insert Processing With an Upsert Trigger

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

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.

Conditional Insert Processing With an Abort Trigger

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

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.

EXPLAIN Request Modifier Terminology for These Examples

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.