Index Only Access Method

The index-only access method can be used in conjunction with any of the index scan-key selection or index scan-key positioning access methods, including the parallel options for these methods. (The parallel options are available only when the DB2 Symmetric Multiprocessing feature is installed.) The processing for the selection does not change from what has already been described for these methods.

However, all of the data is extracted from the index rather than performing a random I/O to the data space. The index entry is then used as the input for any derivation or result mapping that might have been specified on the query.

Where the index-only method is most effective

The optimizer chooses this method when:

• All of the columns that are referenced within the query can be found within a permanent index or within the key columns of a temporary index that the optimizer has decided to create.
• The data values must be able to be extracted from the index and returned to the user in a readable format; in other words, none of the key columns that match the query columns have:
  • Absolute value specified
  • Alternative collating sequence or sort sequence specified
  • Zoned or digit force specified
• The query does not use a left outer join or an exception join.
• For non-SQL users, no variable length or null capable columns can require key feedback.

The following example illustrates a query where the optimizer could choose to perform index only access.

    CREATE INDEX X2
       ON EMPLOYEE(WORKDEPT,LASTNAME,FIRSTNME)
 
    DECLARE BROWSE2 CURSOR FOR
      SELECT FIRSTNME FROM EMPLOYEE
      WHERE WORKDEPT = 'D11'
      OPTIMIZE FOR 99999 ROWS
 

OPNQRYF example:

OPNQRYF FILE((EMPLOYEE))
  QRYSLT('WORKDEPT *EQ ''D11''')

In this example, the database manager uses X2 to position to the index entries for WORKDEPT='D11' and then extracts the value for the column FIRSTNME from those entries.

Note that the index key columns do not have to be contiguous to the leftmost key of the index for index only access to be performed. Any key column in the index can be used to provide data for the index only query. The index is used simply as the source for the data so the database manager can finish processing the query after the selection has been completed.

Note:

Index only access is implemented on a particular table, so it is possible to perform index only access on some or all of the tables of a join query.

PRTSQLINF command messages

The messages created by the PRTSQLINF command to describe this query in an SQL program are as follows:

   SQL4008  Index X2 used for table 1.
   SQL4011  Key row positioning used on table 1.
   SQL4022  Index only access used on table 1.