Index access for UPDATE and MERGE statements

The use of index access for UPDATE and MERGE statements depends on the type of index, the columns that are updated, and the predicates of the MERGE or UPDATE statements.

Start of change Db2 can always use an index for an UPDATE or MERGE operation when no index key columns are updated. However, if index key columns are updated, Db2 can use an index for the UPDATE or MERGE operation only sometimes, such as in the following situations: End of change

For UPDATE statements only, if Db2 can use index access with list prefetch (PREFETCH='L')
If the MERGE or UPDATE statement does not reference a view with the WITH CHECK OPTION attribute, and the MERGE or UPDATE statement contains a predicate for each updated index key column, in one of the following forms:
- index-key-column = expression, where expression is a constant or any expression that can be treated as a literal, including a host variable, parameter marker, or non-column expression
- index-key-column IS NULL

Db2 cannot use a data-partitioned secondary index (DPSI) for a MERGE operation when partition key columns are updated.

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:

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

Db2 can use the index because the statement includes the TRGT.C1 = SRC.C1 predicate 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.