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 changeDb2 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

  • Start of changeIf Db2 can use index access with list prefetch (PREFETCH='L') for the UPDATE or MERGE statementEnd of change
  • 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:
    • Start of changeindex-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 expressionEnd of change
    • index-key-column IS NULL