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 (see
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.
Example: Load Retrieved Columns into a Secondary Index to Improve Performance
In this example, the table bigtable contains 100,000 rows and 20,000 pages.
First, follow these steps to modify the bigtable to use a B-tree structure keyed on three columns:
1. In VDBA, open the Modify Table Structure dialog for bigtable. For more information, see the chapter “Maintaining Storage Structures” and online help.
2. Enable Change Storage Structure and click Structure.
The Structure of Table dialog opens.
3. Select B-tree in the Structure drop-down list, enable col1, col2, and col3 in the Columns group box to specify them as keys, and then click OK.
The Structure of Table dialog closes.
4. Click OK
The Modify Table Structure dialog closes.
Next, a SELECT statement is issued in which the key columns are specified in the WHERE clause. This search requires a full table scan, even though the three columns in question are key columns in the bigtable structure:
select col1, col2, col3 from bigtable
where col1 = 'Colorado', col2 = 17, col3 = 'repo';
Creating a secondary index on the three columns alleviates this problem.
Follow these steps to create a secondary index, with name xbig:
1. In VDBA, open the Create Indexes dialog for bigtable. For more information, see online help.
2. Enter xbig in the Index Name edit control.
3. For each of the key columns, col1, col2, and col3, select the column in the Base Table Columns list box, and click the double-right arrow (>>) to add it to the Index Columns list box, and then click OK.
The index xbig is 500 pages. Issuing the exact same query as before (shown again below) now uses the secondary index, thereby reducing the scan from 20,000 pages to 500 pages:
select col1, col2, col3 from bigtable
where col1 = 'Colorado', col2 = 17, col3 = 'repo';
Aggregates on secondary indexes can be more efficient, because the index is so much smaller than the base table. For example, if there was a secondary index on col1, this aggregate is processed in much less time:
select avg(col1) from bigtable;
Last modified date: 11/28/2023