Index-only access (INDEXONLY='Y')

Db2 uses index-only access when it can avoid accessing data pages because the information in the index satisfies the query. Conversely, when a query requests a column that is not in an index, Db2 must access the associated data pages. Because the index is almost always smaller than the table itself, index-only access paths usually process data more efficiently.

Begin program-specific programming interface information. For SELECT statements Db2 uses index-only access when all columns for a query are found in an index. For UPDATE and DELETE statements, Db2 can use index only access to qualify the selected rows. However, Db2 must access the data pages to modify the data values.

With an index on T(C1, C2), the following queries can use index-only access:

SELECT C1, C2 FROM T WHERE C1 > 0;
SELECT C1, C2 FROM T;
SELECT COUNT(*) FROM T WHERE C1 = 1;

If you create or alter a unique index that is used for index-only access, you can include extra columns in the index. By including the extra columns, you can avoid creating more indexes for index-only access on the extra columns. You specify the INCLUDE clause in a CREATE INDEX statement to add the extra columns. You can also specify ADD INCLUDE COLUMN in an ALTER INDEX statement to append a column to an existing unique index.

Index-only access to data is not possible for any step that uses list prefetch.

Padded indexes prevent index-only access to data in the following situations:

Varying-length data is returned
A VARCHAR column has a LIKE predicate
An index on expression is the left-hand side of a LIKE predicate, and the index does not contain varying length data.

If access is by more than one index, INDEXONLY is Y for a step with access type MX, or DX. The data pages are not accessed until all the steps for intersection (MI or DI) or union (MU or DU) take place.

When an SQL application uses index-only access for a rowid column, the application claims the table space or table space partition. As a result, contention might occur between the SQL application and a utility that drains the table space or partition. Index-only access to a table for a rowid column is not possible if the associated table space or partition is in an incompatible restrictive state. For example, an SQL application can make a read claim on the table space only if the restrictive state allows readers. End program-specific programming interface information.