Designing Tables and Indexes for Performance › Table Designs for Special Situations › Building an Index on the Fly

Building an Index on the Fly

In situations in which you are storing data through a key that is designed for a high speed insert strategy with minimal table maintenance, avoid secondary indexes. Scanning billions of rows is not an efficient use of resources.

A solution may be to build a lookup table that acts as a sparse index. This lookup table will contain nothing more than your ascending key values. One example would be dates (one date per month for every month possible in your database). If the historical data is organized and partitioned by the date, and you have only one date per month (to sub categorize the data), you can use the new sparse index to access the data you need. Using user-supplied dates as starting and ending points, the look-up table can be used to fill the gap with the dates in between. This practice provides the initial path to the history data. Read access is performed by constructing a key during SELECT processing.

In the following examples, we access an account history table (ACCT_HIST) that has a key on HIST_EFF_DTE, ACCT_ID, and the date lookup table that is called ACCT_HIST_DATES, which contains one column and one row for each legitimate date value corresponding to the HIST_EFF_DTE column of the ACCT_HIST table.