EXPLAIN and UPDATE (Upsert Form) Conditional Steps - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
ykx1561500561173.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

The EXPLAIN modifier is useful in determining the conditional steps in UPDATE (Upsert Form) processing. Atomic upsert conditional steps are insert operations that are performed after an unconditional update operation does not meet its matching condition.

Database Object DDL for Examples

The following DDL statements create the tables accessed by the explained DML request examples.

CREATE TABLE t1 (x1 INTEGER, y1 INTEGER);
   
CREATE TABLE t2 (x2 INTEGER NOT NULL,y2 INTEGER NOT NULL);

Simple Conditional Upsert Processing Without Trigger or Join Index Steps

The following example demonstrates simple conditional insert processing into table t1 without trigger or join index steps:

EXPLAIN 
UPDATE t1
SET y1 = 3 WHERE x1 = 2
ELSE INSERT t1(2, 3);
1) First, we do a single-AMP UPDATE from TEST.t1 by way of the
   primary index "TEST.t1.x1 = 2" with no residual conditions. If
   the row cannot be found, then we do an INSERT into TEST.t1.
...

Both the unconditional update attempt and the conditional insert are combined in a single step.

More Complex Upsert Processing With Join Index Steps

The following example demonstrates slightly more complicated upsert 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);

A portion of the EXPLAIN output for the UPDATE is below:

EXPLAIN 
UPDATE t1
SET y1 = 3 WHERE x1 = 2
ELSE INSERT t1(2, 3);
...
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 = 2" 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 = 3") 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 = 2" 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 = 2" with no residual conditions. If the
        row cannot be found, then we do an INSERT into TEST.t1.
     3) If no update in 5.2, we do an INSERT into Spool 3.
     4) 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).
...

The EXPLAIN report is more complicated because of the join index j. Notice the instances of the phrase “If no update in <step number>” in steps 5.3, 5.4, 6, and 7, indicating the operations undertaken if the match condition for update was not met. 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 (step 5.3). The join index, j, is updated by steps 1 through 4, 6, and 7.

Upsert With a Simple Trigger

The following upsert statement involves a simple trigger. The relevant phrases in the EXPLAIN report appear in boldface type.

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

A portion of the EXPLAIN output is below:

EXPLAIN 
UPDATE t1
SET y1 = 3 WHERE x1 = 2
ELSE INSERT t1(2, 3);
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 = 2" with no residual conditions. If the
        row cannot be found, then we do an INSERT into TEST.t1.
     2) 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 EXPLAIN report is moderately complex because of the trigger. Step 1.1 handles the unconditional update attempt and the conditional insert, while step 1.2 handles the triggered update to table t2.

Notice the phrase “If no update in <step number>” in step 1.2, indicating that the step performs only if the match condition for update was not met.

Upsert With an Abort Trigger

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 appear in boldface type.

EXPLAIN 
UPDATE t4
SET y4 = 3 WHERE x4 = 2
ELSE INSERT t4(2, 3);
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 = 2" 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.
...

Notice the instances of the phrase “If no update in <step number>” in steps 2.1, 2.2, 3, and 4, indicating that the step performs only if an update was not successful.

Step 1.1 handles the unconditional update attempt to t4, while step 1.2 performs the update processing defined by the trigger. Steps 2 and 3 continue to perform trigger-related operations, while step 4 performs the upsert-specified insert operation if the update to t4 fails.

Complicated Upsert With Multiple Triggers

This example uses the 5 tables defined by the following DDL table definition requests:

CREATE TABLE t7 (x7 INTEGER, y7 INTEGER);

CREATE TABLE t8 (x8 INTEGER, y8 INTEGER);

CREATE TABLE t9 (x9 INTEGER, y9 INTEGER);

CREATE TABLE t10 (x10 INTEGER, y10 INTEGER);
   
CREATE TABLE t11 (x11 INTEGER, y11 INTEGER);

The example also uses the following definitions for triggers r6 through r10:

CREATE TRIGGER r6 ENABLED AFTER UPDATE ON t1
 (UPDATE t7 SET y7 = 7 WHERE x7 = 6
  ELSE INSERT t7(6, 7); );
   
CREATE TRIGGER r7 ENABLED AFTER UPDATE ON t7
 (UPDATE t8 SET y8 = 8 WHERE x8 = 7
 ELSE INSERT t8(7, 8); );
   
CREATE TRIGGER r8 ENABLED AFTER UPDATE ON t7
 (UPDATE t9 SET y9 = 8 WHERE x9 = 7
 ELSE INSERT t9(7, 8); );
   
CREATE TRIGGER r9 ENABLED AFTER INSERT ON t7
 (UPDATE t10 SET y10 = 9 WHERE x10 = 8
 ELSE INSERT t10(8, 9); );
   
CREATE TRIGGER r10 ENABLED AFTER INSERT ON t7
 (UPDATE t11 SET y11 = 10 WHERE x11 = 9
 ELSE INSERT t11(9, 10); );
   
EXPLAIN 
UPDATE t1
SET y1 = 20 WHERE x1 = 30
ELSE INSERT t1(30, 20);

The relevant phrases in the EXPLAIN report appear in boldface type.

1) First, we do a single-AMP UPDATE from Test.t1 by way of the 
   primary index Test.t1.x1 = 30 with no residual conditions.
2) Next, we execute the following steps in parallel.
      1) We do a single-AMP UPDATE from Test.t2 by way of the 
         primary index Test.t2.x2 = 1 with no residual conditions.
      2) If no update in 2.1, we do a single-AMP UPDATE
         from Test.t3 by way of the primary index Test.t3.x3 = 2
         with no residual conditions. If the row cannot be found, 
         then we do an INSERT into Test.t3.
3) If no update in 2.1, we do a single-AMP UPDATE from 
   Test.t4 by way of the primary index Test.t4.x4 = 3 with no 
   residual conditions. If the row cannot be found, then we do 
   an INSERT into Test.t4.
4) If no update in 2.1, we do an INSERT into Test.t2.
5) If no update in 2.1, we do a single-AMP UPDATE from 
   Test.t5 by way of the primary index Test.t5.x5 = 4 with no 
   residual conditions. If the row cannot be found, then we do 
   an INSERT into Test.t5.
6) If no update in 2.1, we do a single-AMP UPDATE from 
   Test.t6 by way of the primary index Test.t6.x6 = 5 with no 
   residual conditions. If the row cannot be found, then we do 
   an INSERT into Test.t6.
7) If no update in 2.1, we do an INSERT into Test.t1.
...

This EXPLAIN report is more complicated because of triggers r6 through r10. Notice the instances of the phrase “If no update in <step number>” in steps 2.2, 3, 4, 5, 6 and 7, indicating steps that are taken only if an unconditional update operation fails.

Only step 1 and step 7 relate directly to the atomic upsert statement. Steps 2 through 6 pertain to the triggers r6 through r10.

Complicated Upsert That Disables Triggers r6 Through r10

This example disables the triggers r6 through r10 from the previous example and invokes the following newly defined triggers. The DDL requests are as follows:

ALTER TRIGGER r6 DISABLED;
ALTER TRIGGER r7 DISABLED;
ALTER TRIGGER r8 DISABLED;
ALTER TRIGGER r9 DISABLED;
ALTER TRIGGER r10 DISABLED;
   
CREATE TRIGGER r11 ENABLED AFTER UPDATE ON t1
(UPDATE t7 SET y7 = 7 WHERE x7 = 6
 ELSE INSERT t7(6, 7););
   
CREATE TRIGGER r12 ENABLED AFTER UPDATE ON t7
(UPDATE t8 SET y8 = 8 WHERE x8 = 7
 ELSE INSERT t8(7, 8););
   
CREATE TRIGGER r13 ENABLED AFTER UPDATE ON t7
(UPDATE t9 SET y9 = 8 WHERE x9 = 7
 ELSE INSERT t9(7, 8););
   
CREATE TRIGGER r14 ENABLED AFTER INSERT ON t7
(UPDATE t10 SET y10 = 9 WHERE x10 = 8
 ELSE INSERT t10(8, 9););
   
CREATE TRIGGER r15 ENABLED AFTER INSERT ON t7
(UPDATE t11 SET y11 = 10 WHERE x11 = 9
 ELSE INSERT t11(9, 10););
EXPLAIN 
UPDATE t1
SET y1 = 20 WHERE x1 = 30
ELSE INSERT t1(30, 20);

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

1) First, we do a single-AMP UPDATE from Test.t1 by way of
   the primary index Test.t1.x1 = 30 with no residual 
   conditions.
2) Next, we execute the following steps in parallel.
      1) We do a single-AMP UPDATE from Test.t7 by way of 
         the primary index Test.t7.x7 = 6 with no residual 
         conditions.
      2) If no update in 2.1, we do a single-AMP 
         UPDATE from Test.t8 by way of the primary index 
         Test.t8.x8 = 7 with no residual conditions. If 
         the row cannot be found, then we do an INSERT into 
         Test.t8.
3) If no update in 2.1, we do a single-AMP UPDATE from 
   Test.t9 by way of the primary    index Test.t9.x9 = 7 with 
   no residual conditions. If the row cannot be found, then
   we do an INSERT into Test.t9.
4) If no update in 2.1, we do an INSERT into Test.t7.
5) If no update in 2.1, we do a single-AMP UPDATE from 
   Test.t10 by way of the primary index Test.t10.x10 = 8 with
   no residual conditions. If the row cannot be found,then we 
   do an INSERT into Test.t10.
6) If no update in 2.1, we do a single-AMP UPDATE from 
   Test.t11 by way of the primary index Test.t11.x11 = 9 with 
   no residual conditions. If the row cannot be found,
   then we do an INSERT into Test.t11.
7) If no update in 2.1, we do an INSERT into Test.t1.
...

Notice the instances of the phrase “If no update in <step number>” in steps 2.2, 3, 4, 5, 6, and 7, indicating operations that are performed only if an update does not succeed.

Only steps 1 and 7 relate directly to the atomic upsert statement. The other steps all perform triggered actions specified by triggers r11 through r15.

Complicated Upsert That Disables Triggers r11 Through r15

This example disables the triggers r11 through r15 from the previous example and invokes the following newly defined triggers. The DDL statements to disable these triggers are as follows:

ALTER TRIGGER r11 DISABLED;
ALTER TRIGGER r12 DISABLED;
ALTER TRIGGER r13 DISABLED;
ALTER TRIGGER r14 DISABLED;
ALTER TRIGGER r15 DISABLED;
   
CREATE TRIGGER r16 ENABLED AFTER INSERT ON t1
(UPDATE t12 SET y12 = 11 WHERE x12 = 10
 ELSE INSERT t12(10, 11););
   
CREATE TRIGGER r17 ENABLED AFTER UPDATE ON t12
(UPDATE t13 SET y13 = 12 WHERE x13 = 11
 ELSE INSERT t13(11, 12););
   
CREATE TRIGGER r18 ENABLED AFTER UPDATE ON t12
(UPDATE t14 SET y14 = 13 WHERE x14 = 12
 ELSE INSERT t14(12, 13););
   
CREATE TRIGGER r19 ENABLED AFTER INSERT ON t12
(UPDATE t15 SET y15 = 14 WHERE x15 = 13
 ELSE INSERT t15(13, 14););
   
CREATE TRIGGER r20 ENABLED AFTER INSERT ON t12
(UPDATE t16 SET y16 = 14 WHERE x16 = 13
 ELSE INSERT t16(13, 14););

Now, the EXPLAIN request:

EXPLAIN 
UPDATE t1
SET y1 = 20 WHERE x1 = 30
ELSE INSERT t1(30, 20);

The relevant phrases in the EXPLAIN report appear in boldface type.

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 = 30 with no residual conditions.
      If the row cannot be found, then we do an INSERT into
      Test.t1.
   2) If no update in 1.1, we do a single-AMP UPDATE from Test.t12 
      by way of the primary index Test.t12.x12 = 10 with no residual 
      conditions.
   3) If no update in 1.2, we do a single-AMP UPDATE from Test.t13 
      by way of the primary index Test.t13.x13 = 11 with no residual 
      conditions. If the row cannot be found, then we do an INSERT
      into Test.t13.
2) Next, if no update in 1.2, we do a single-AMP UPDATE from 
   Test.t14 by way of the primary index Test.t14.x14 = 12 with no 
   residual conditions. If the row cannot be found, then we do an
   INSERT into Test.t14.
3) If no update in 1.2, we do an INSERT into Test.t12.
4) If no update in 1.2, we do a single-AMP UPDATE from Test.t15 by way
   of the primary index Test.t15.x15 = 13 with no residual conditions.
   If the row cannot be found,then we do an INSERT into Test.t15.
5) If no update in 1.2, we do a single-AMP UPDATE from Test.t16 by way 
   of the primary index Test.t16.x16 = 13 with no residual conditions. 
   If the row cannot be found,then we do an INSERT into Test.t16.
...

Notice the instances of the phrase “If no update in <step number>” in steps 1.2, 1.3, 2, 3, 4, and 5 indicating operations that are performed only if an update does not succeed.

Only step 1.1 relates directly to the atomic upsert statement. The other steps all perform triggered actions specified by triggers r16 through r20.

Complicated Upsert With Different Triggers Than the Previous Example

This example disables the triggers r16 through r20 from the previous example and invokes the following newly defined triggers. The query plan for this example is identical to that of the previous example, as the EXPLAIN report at the end confirms.

ALTER TRIGGER r16 DISABLED;
ALTER TRIGGER r17 DISABLED;
ALTER TRIGGER r18 DISABLED;
ALTER TRIGGER r19 DISABLED;
ALTER TRIGGER r20 DISABLED;
   
CREATE TRIGGER r21 ENABLED AFTER INSERT ON t1
(UPDATE t17 SET y17 = 11 WHERE x17 = 10
 ELSE INSERT t17(10, 11););
   
CREATE TRIGGER r22 ENABLED AFTER UPDATE ON t17
(UPDATE t18 SET y18 = 12 WHERE x18 = 11
 ELSE INSERT t18(11, 12););
   
CREATE TRIGGER r23 ENABLED AFTER UPDATE ON t17
(UPDATE t19 SET y19 = 13 WHERE x19 = 12
 ELSE INSERT t19(12, 13););
   
CREATE TRIGGER r24 ENABLED AFTER INSERT ON t17
(UPDATE t20 SET y20 = 14 WHERE x20 = 13
 ELSE INSERT t20(13, 14););
   
CREATE TRIGGER r25 ENABLED AFTER INSERT ON t17
(UPDATE t21 SET y21 = 14 WHERE x21 = 13
 ELSE INSERT t21(13, 14););
   
EXPLAIN 
UPDATE t1
SET y1 = 20 WHERE x1 = 30
ELSE INSERT t1(30, 20);

The relevant phrases in the EXPLAIN report appear in boldface type.

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 = 30 with no residual conditions. If the row 
         cannot be found, then we do an INSERT into Test.t1.
      2) If no update in 1.1, we do a single-AMP UPDATE from 
         Test.t17 by way of the primary index Test.t17.x17 = 10 with 
         no residual conditions.
      3) If no update in 1.2, we do a single-AMP UPDATE from 
         Test.t18 by way of the primary index Test.t18.x18 = 11 with 
         no residual conditions. If the row cannot be found, then we 
         do an INSERT into Test.t18.
2) Next, if no update in 1.2, we do a single-AMP UPDATE from 
   Test.t19 by way of the primary index Test.t19.x19 = 12 with no 
   residual conditions. If the row cannot be found, then we do an 
   INSERT into Test.t19.
3) If no update in 1.2, we do an INSERT into Test.t17.
4) If no update in 1.2, we do a single-AMP UPDATE from Test.t20 
   by way of the primary index Test.t20.x20 = 13 with no residual 
   conditions. If the row cannot be found, then we do an INSERT into 
   Test.t20.
5) If no update in 1.2, we do a single-AMP UPDATE from Test.t21 
   by way of the primary index Test.t21.x21 = 13 with no residual 
   conditions. If the row cannot be found, then we do an INSERT into 
   Test.t21.
...

Notice the instances of the phrase “If no update in <step number>” in steps 1.2, 1.3, 2, 3, 4, and 5 indicating operations that are performed only if an update does not succeed.

Only step 1.1 relates directly to the atomic upsert statement. The other steps all perform triggered actions specified by triggers r21 through r25.

Complicated Upsert With Multiple Triggers From Previous Examples

This example performs with triggers r1 through r15 enabled, a set of conditions that adds many steps. Note that triggers r21 through r25 are already enabled in the previous example.

ALTER TRIGGER r1 ENABLED;
ALTER TRIGGER r2 ENABLED;
ALTER TRIGGER r3 ENABLED;
ALTER TRIGGER r4 ENABLED;
ALTER TRIGGER r5 ENABLED;
ALTER TRIGGER r6 ENABLED;
ALTER TRIGGER r7 ENABLED;
ALTER TRIGGER r8 ENABLED;
ALTER TRIGGER r9 ENABLED;
ALTER TRIGGER r10 ENABLED;
ALTER TRIGGER r11 ENABLED;
ALTER TRIGGER r12 ENABLED;
ALTER TRIGGER r13 ENABLED;
ALTER TRIGGER r14 ENABLED;
ALTER TRIGGER r15 ENABLED;
   
EXPLAIN 
UPDATE t1
SET y1 = 20 WHERE x1 = 30
ELSE INSERT t1(30, 20);

The relevant phrases in the EXPLAIN report appear in boldface type.

1) First, we do a single-AMP UPDATE from Test.t1 by way of the primary
   index Test.t1.x1 = 30 with no residual conditions.
2) Next, we execute the following steps in parallel.
      1) We do a single-AMP UPDATE from Test.t2 by way of the primary 
         index Test.t2.x2 = 1 with no residual conditions.
      2) If no update in 2.1, we do a single-AMP UPDATE from 
         Test.t3 by way of the primary index Test.t3.x3 = 2 with no 
         residual conditions. If the row cannot be found, then we do 
         an INSERT into Test.t3.
3) If no update in 2.1, we do a single-AMP UPDATE from Test.t4 
   by way of the primary index Test.t4.x4 = 3 with no residual 
   conditions. If the row cannot be found, then we do an INSERT into 
   Test.t4.
4) If no update in 2.1, we do an INSERT into Test.t2.
5) If no update in 2.1, we do a single-AMP UPDATE from Test.t5 
   by way of the primary index Test.t5.x5 = 4 with no residual
   conditions. If the row cannot be found, then we do an INSERT
   into Test.t5.
6) If no update in 2.1, we do a single-AMP UPDATE from Test.t6 by 
   way of the primary index Test.t6.x6 = 5 with no residual conditions. 
   If the row cannot be found, then we do an INSERT into Test.t6.
7) We execute the following steps in parallel.
      1) We do a single-AMP UPDATE from Test.t7 by way of the primary
         index Test.t7.x7 = 6 with no residual conditions.
      2) If no update in 7.1, we do a single-AMP UPDATE from 
         Test.t8 by way of the primary index Test.t8.x8 = 7 with no 
         residual conditions. If the row cannot be found, then we do 
         an INSERT into Test.t8.
8) If no update in 7.1, we do a single-AMP UPDATE from Test.t9 
   by way of the primary    index Test.t9.x9 = 7 with no residual 
   conditions. If the row cannot be found, then we do an INSERT into 
   Test.t9.
9) If no update in 7.1, we do an INSERT into Test.t7.
10)If no update in 7.1, we do a single-AMP UPDATE from Test.t10 
   by way of the primary index Test.t10.x10 = 8 with no residual 
   conditions. If the row cannot be found,then we do an INSERT into 
   Test.t10.
11)If no update in 7.1, we do a single-AMP UPDATE from Test.t11 
   by way of the primary index Test.t11.x11 = 9 with no residual 
   conditions. If the row cannot be found, then we do an INSERT into 
   Test.t11.
12) If no update in 7.1, we do an INSERT into Test.t1.
13) We execute the following steps in parallel.
      1) If no update in 1, we do a single-AMP UPDATE from 
         Test.t12 by way of the primary index Test.t12.x12 = 10 
         with no residual conditions.
      2) If no update in 13.1, we do a single-AMP UPDATE 
         from Test.t13 by way of the primary index Test.t13.x13 = 11 
         with no residual conditions. If the row cannot be found, 
         then we do an INSERT into Test.t13.
14) If no update in 13.1, we do a single-AMP UPDATE from Test.t14 
    by way of the primary index Test.t14.x14 = 12 with no residual 
    conditions. If the row cannot be found,then we do an INSERT into 
    Test.t14.
15) If no update in 13.1, we do an INSERT into Test.t12.
16) If no update in 13.1, we do a single-AMP UPDATE from Test.t15 
    by way of the primary index Test.t15.x15 = 13 with no residual 
    conditions. If the row cannot be found,then we do an INSERT into
    Test.t15.
17) If no update in 13.1, we do a single-AMP UPDATE from Test.t16 
    by way of the primary index Test.t16.x16 = 13 with no residual 
    conditions. If the row cannot be found,then we do an INSERT into
    Test.t16.
18) We execute the following steps in parallel.
      1) If no update in 1, we do a single-AMP UPDATE from Test.t17 
         by way of the primary index Test.t17.x17 = 10 with no
         residual conditions.
      2) If no update in 18.1, we do a single-AMP UPDATE from 
         Test.t18 by way of the primary index Test.t18.x18 = 11 
         with no residual conditions. If the row cannot be found,
         then we do an INSERT into Test.t18.
19) If no update in 18.1, we do a single-AMP UPDATE from Test.t19 
    by way of the primary index Test.t19.x19 = 12 with no residual 
    conditions. If the row cannot be found, then we do an INSERT into 
    Test.t19.
20) If no update in 18.1, we do an INSERT into Test.t17.
21) If no update in 18.1, we do a single-AMP UPDATE from Test.t20 
    by way of the primary index Test.t20.x20 = 13 with no residual 
    conditions. If the row cannot be found, then we do an INSERT into 
    Test.t20.
22) If no update in 18.1, we do a single-AMP UPDATE from Test.t21 
    by way of the primary index Test.t21.x21 = 13 with no residual 
    conditions. If the row cannot be found, then we do an INSERT into 
    Test.t21.
...

Notice the instances of the phrase “If no update in <step number>” in steps 2.2, 3, 4, 5, 6, 7.2, 8, 9, 10, 11, 12, 13.1, 13.2, 14, 15, 16, 17, 18.1, 18.2, 19, 20, 21,and 22, indicating operations that are performed only if an update does not succeed.

Only steps 1 and 12 relate directly to the atomic upsert statement. The other steps all perform triggered actions specified by the triggers.

Also note that steps 13.1 and 18.1 are based on the action in step 1, because the triggers r16 and r21 are defined with an insert triggering statement on the subject table t1.

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.