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 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 follows:
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 follows:
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 steps. Triggers r21 through r25 are 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 is 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. |