Getting Started › Application Design Considerations › Index-Only Processing

Index-Only Processing

In index-only processing data is retrieved, when possible, from the index only, eliminating the cost of accessing the actual row in the data area. For example, consider an online application to look up an account by name when the customer does not know their account number:

 SELECT NAME, STREET, CITY, ST, ZIP, ACCOUNT_NBR
 FROM ACCOUNTS
 WHERE NAME BETWEEN :NAME_BEG AND :NAME_END
 OPTIMIZE FOR 20 ROWS;         -- ONLY 20 ROWS ON A SCREEN

This query can use index-only processing if the following two conditions are met:

• There is a key with all the columns in the query. This includes columns in the SELECT list, WHERE clause, and anywhere else in the query. In this case key (NAME, ST, ZIP, CITY, STREET, ACCOUNT_NBR) would work. Notice that the order of the columns in the key is not important, except for being good for selection. Therefore, the NAME column is first and the remaining columns are included only so that all the columns referenced are available from the index.
• Isolation level U is specified for the plan, so that no lock is required.

If the ACCOUNTS table Native Key is ACCOUNT_NBR, accessing rows in NAME sequence could cost an I/O for every row retrieved. Indexes usually have 100 or more entries per DXX block, requiring just one logical I/O instead of 21 I/Os.

Note: If you add columns to an index for index-only retrieval that are updated frequently, the cost of updating the index may outweigh the advantage of index-only retrieval.