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:
5. In VDBA, open the Create Indexes dialog for bigtable. For more information, see online help.
6. Enter xbig in the Index Name edit control.
7. 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;