Maintaining histories of data

I would like to maintain a history of rows in my database: for each row I would like the date when the row was created and the date when it was superceded (if any). For example, I might create a table employee with columns valid_from, valid_until, name, and salary with rows like the following:

valid_from  valid_until   name          salary
1/1/93      1/1/94        John Doe      $40,000
1/1/94      NULL          John Doe      $50,000

Selecting only the rows that were valid at a particular date is not difficult: I can use a construct like

SELECT salary
FROM employee
WHERE valid_from <= SYSDATE
AND (valid_to IS NULL OR valid_to >= SYSDATE) AND name = 'John Doe';

But is it possible to construct an index that will support this sort of query?

Thanks,

Tim Boemker Received on Wed Nov 02 1994 - 14:54:42 CET