16.10 - EXPLAIN and UPDATE (Upsert Form) Conditional Steps - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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);
   
    *** Help information returned. 4 rows.
    *** Total elapsed time was 1 second.
   
Explanation
-----------------------------------------------------------------------
  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.
  -> No rows are returned to the user as the result of statement 1.

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);
   
     EXPLAIN UPDATE t1
             SET y1 = 3 WHERE x1 = 2
             ELSE INSERT t1(2, 3);
   
    *** 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 = 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).
  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.

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););
   
     EXPLAIN UPDATE t1
             SET y1 = 3 WHERE x1 = 2
             ELSE INSERT t1(2, 3);
   
    *** 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 = 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.
  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 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);
   
    *** 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 = 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.
  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.

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);
   
    *** Help information returned. 41 rows.
    *** Total elapsed time was 1 second.

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

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

This EXPLAIN report is more complicated because of the triggers r6, r7, r8, r9, and 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, r7, r8, r9, and r10.

Complicated Upsert That Disables Triggers r6 Through r11

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);
   
   *** Help information returned. 23 rows.
   *** Total elapsed time was 1 second.

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

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

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);
   
    *** Help information returned. 25 rows.
    *** Total elapsed time was 1 second.

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

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

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);
   
    *** Help information returned. 25 rows.
    *** Total elapsed time was 1 second.
   

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

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

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);
   
    *** Help information returned. 82 rows.
    *** Total elapsed time was 1 second.

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

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

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.