Secondary Indexes and Performance
Secondary indexes are generally used to index into the base table they see, although if the query can be executed in the secondary index alone, the base table need not be visited. Using secondary indexes to help complete queries that are otherwise executed on the base table can dramatically reduce the query execution time.
For example, assume a secondary index exists on the name column for the employee table, and the following query is executed:
select empno, age, name
from employee
where name like 'A%';
First, records beginning with an “A” in the secondary index are located, and using the tidp column, each tidp is used to do a tid lookup into the employee table, to get the rest of the information about the employee, namely empno and age.
Tids identify every row on every data page.
Both the secondary index and the base table are used in this query. However, had the retrieval asked only for employee.name rather than empno and age, the base table is not used, and the number of disk I/Os executed is reduced by more than 50%.
Even in some situations requiring scans of the entire table, you can dramatically improve performance by loading the columns retrieved into the secondary index, so that probing the base table is not necessary. An example is shown in
Example: Load Retrieved Columns into a Secondary Index to Improve Performance.