Predicates that qualify for direct row access

For a query to qualify for direct row access, the search condition must be a Boolean term stage 1 predicate, and must meet certain conditions.

Begin program-specific programming interface information. SQL queries that meet the following criteria qualify for direct row access.

  • A simple Boolean term predicate of the form COL=noncolumn expression, where COL has the ROWID data type and noncolumn expression contains a row ID
  • A simple Boolean term predicate of the form COL IN list, where COL has the ROWID data type and the values in list are row IDs, and an index is defined on COL
  • A compound Boolean term that combines several simple predicates using the AND operator, and one of the simple predicates fits description 1 or 2
  • A simple Boolean term predicate of the form RID (table designator) = noncolumn expression, where noncolumn expression contains a result of the RID built-in function.
  • A compound Boolean term that combines several simple predicates using the AND operator, and one of the simple predicates fits description 4.

However, just because a query qualifies for direct row access does not mean that access path is always chosen. If Db2 determines that another access path is better, direct row access is not chosen.

Examples

In the following predicate example, ID is a ROWID column in table T1. A unique index exists on that ID column. The host variables are of the ROWID type.

WHERE ID IN (:hv_rowid1,:hv_rowid2,:hv_rowid3)

The following predicate also qualifies for direct row access:

WHERE ID = ROWID(X'F0DFD230E3C0D80D81C201AA0A280100000000000203')

Searching for propagated rows

If rows are propagated from one table to another, do not expect to use the same row ID value from the source table to search for the same row in the target table, or vice versa. This does not work when direct row access is the access path chosen.

Assume that the host variable in the following statement contains a row ID from SOURCE:

SELECT * FROM TARGET
  WHERE ID = :hv_rowid

Because the row ID location is not the same as in the source table, Db2 probably cannot find that row. Search on another column to retrieve the row you want. End program-specific programming interface information.