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 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, the MERGE inserts a new row into t4 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, 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 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. |