The database manager can build a temporary index from an existing index without having to read all of the rows in the table data. Generally, this selection method is one of the most efficient. The temporary index that is created only contains entries for rows that meet the selection predicates. This is similar to a select/omit logical file or sparse index. The optimizer chooses this method when all of the following are true:
To use the index-from-index access method, the database manager uses key positioning on the permanent index with the query selection criteria and builds index entries in the temporary index using the ordering column from the selected rows. 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
In the previous example, key positioning will be performed using index X1. For each entry selected, an entry will be created into the temporary index that is being built. The temporary index will have a key column LASTNAME.
For more information, see Database Performance and Query Optimization in the Information Center .