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

Range of Data Access

Accessing a range of data is a little more complicated than simply retrieving the most recent account history data. Use the sparse index history date table to build the key on demand. Apply the range of dates to the date range table, and then join the date range table to the history table.

SELECT {columns}

FROM ACCT_HIST HIST
INNER JOIN
     ACCT_HIST_DATES DTE
ON HIST.HIST_EFF_DTE = DTE.EFF_DTE
WHERE HIST.ACCT_ID = :ACCT-ID
AND HIST.HIST_EFF_DTE BETWEEN :BEGIN-DTE AND :END-DTE;