Index-from-index access method

The database manager can build a temporary index from an existing index without having to read all of the rows in the dataspace. Generally speaking, this selection method is one of the most efficient. The temporary index that is created contains entries for only those rows that meet the selection predicates. This is similar to the index created by a select/omit logical file (commonly referred to as a sparse index).

Where the index-from-index access method is most effective

The optimizer chooses this method when:

• The query requires an index because it uses grouping, ordering, or join processing.
• A permanent index exists that has selection columns as the left-most keys and the left-most keys are very selective.
• The selection columns are not the same as the ordering, grouping, or join-to columns.

How the query optimizer uses this method

To use the index-from-index access method, the database manager:

1. Uses index scan-key positioning on the permanent index with the query selection criteria
2. Builds index entries in the new temporary index using selected row entries.

The result is an index containing entries in the required key sequence for rows that match the selection criteria.

A common index-from-index access method example follows:

    CREATE INDEX X1 ON EMPLOYEE(WORKDEPT)
 
    DECLARE BROWSE2 CURSOR FOR
      SELECT * FROM EMPLOYEE
      WHERE WORKDEPT = 'D11'
      ORDER BY LASTNAME
      OPTIMIZE FOR 99999 ROWS

OPNQRYF example:

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

For this example, a temporary select/omit index is created with the primary key column LASTNAME. It contains index entries for only those rows where WORKDEPT = 'D11'. If WORKDEPT = 'D11', less than approximately 20% of the rows are selected.

PRTSQLINF command messages

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

   SQL4012  Index created from index X1 for table 1.
   SQL4011  Key row positioning used on table 1.

Alternatives to the index-from-index method

Rather than using the index-from-index access method, you can use the query sort routine. See Sort access method for more information.

This decision is based on the number of rows to be retrieved.