Index access for MERGE
Db2 can always use an index for a MERGE operation when no index key columns are updated. Db2 can sometimes use an index when key columns are updated, but only when certain conditions are met.
- The MERGE statement contains a corresponding predicate in one
of the following forms, for each updated index key column:
index-key-column = constantindex-key-column IS NULL
- If a view is involved, WITH CHECK OPTION is not specified.
Examples for when Db2 can and cannot use index access for MERGE statements
- Db2 can use index access when the index includes no columns that are being updated
- For example, assume that Index I1 on table T1 column(C1), and consider the following
statement:
Db2 can use the index because the statement includes theMERGE INTO T1 TRGT USING (VALUES (?, ?) FOR ? ROWS) AS SRC (C1,C2) ON (TRGT.C1 = SRC.C1) WHEN MATCHED THEN UPDATE SET TRGT.C2 = SRC.C2 WHEN NOT MATCHED THEN INSERT (C1,C2) VALUES (SRC.C1, SRC.C2) NOT ATOMIC CONTINUE ON SQLEXCEPTION;TRGT.C1 = SRC.C1predicate in the ON clause and TRGT.C1 is not an updated column. - Db2 cannot use index access when the index includes columns that are being updated
- For example, assume
that an index I2 exists on table columns (C1, C2) and consider the
following statement:
MERGE INTO T1 TRGT USING (VALUES (?, ?, ?) FOR ? ROWS) AS SRC (C1,C2,C3) ON TRGT.C1 = SRC.C1 AND TRGT.C2 = SRC.C2 WHEN MATCHED THEN UPDATE SET TRGT.C1 = SRC.C1 ,TRGT.C2 = SRC.C2 ,TRGT.C3 = SRC.C3 WHEN NOT MATCHED THEN INSERT (C1,C2.C3) VALUES (SRC.C1, SRC.C2, SRC.C3) NOT ATOMIC CONTINUE ON SQLEXCEPTION;In this case, the ON clause predicates mean that the values of C1 and C2 must already contain the values that they are updated to by the SET clause. Nevertheless, because the SET clause contains these columns, the use of I2 for index access is prevented. You can remove C1 and C2 from the SET clause to enable Db2 to use the index.
For another example, assume that an index I1 exists on columns (C1,C2,C3), and consider the following statement:
MERGE INTO T1 TRGT USING (VALUES (?, ?, ?, ?) FOR ? ROWS) AS SRC (C1,C2,C3,C4) ON TRGT.C1 = SRC.C1 AND (TRGT.C2 = SRC.C2 OR TRGT.C3 = SRC.C3) WHEN MATCHED THEN UPDATE SET TRGT.C2 = SRC.C2 ,TRGT.C3 = SRC.C3 ,TRGT.C4 = SRC.C4 WHEN NOT MATCHED THEN INSERT (C1,C2.C3,C4) VALUES (SRC.C1, SRC.C2, SRC.C3, SRC.C4) NOT ATOMIC CONTINUE ON SQLEXCEPTION;In this case, columns C2 and C3 are included in the index that is being updated, and they are also included in index I1. Db2 is prevented from using index I1 when checking for matching rows.
The following alternative approaches can enable index access in this case:- Create an index on column C1 only.
- Use UPDATE and INSERT statements in the application instead of MERGE. Db2 supports index access for UPDATE by using the I1 index for list prefetch when searching for rows to UPDATE. List prefetch is not supported for MERGE statements.