Oracle 10g's New Row Timestamps

One of the systems I work on, maintains a cache of data which is reloaded from the database every time an update is made. Using a cache, rather than hitting the database each time, has some significant performance advantages. However, one of the downsides is the time taken to load the cache. Due to the large amount of data involved, sometimes reloading the cache takes up to 15 minutes!

The main reason for this is that because we don't know which rows of data have changed, we end up loading the entire cache each time. This means that we will reload 50,000 rows of data even if only 1 row has changed!

One solution would be to add a "last_updated" column to each of our tables and use that in our cache-refresh query. A better way, however, would be to use an exciting new feature that comes in Oracle 10g...

In Oracle 10g, a new pseudocolumn called ORA_ROWSCN is available on every row which "returns the conservative upper bound system change number (SCN) of the most recent change to the row". This is useful for determining approximately when a row was last updated. Also, the SCN_TO_TIMESTAMP function can be used to convert an SCN to a timestamp!

So coming back to the cache problem, we can determine which rows were modified simply by writing a query which gets all rows having an SCN_TO_TIMESTAMP(ORA_ROWSCN) greater than the time that the cache was last reloaded!

Sadly, we're still on Oracle 9i so will have to wait to use this awesome new feature:(

Example

SELECT ORA_ROWSCN, last_name FROM employees
WHERE first_name = 'FAHD';

UPDATE employees SET salary = salary*10
WHERE first_name = 'FAHD';

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), last_name FROM employees
WHERE first_name = 'FAHD';

Reference

Oracle®: ORA_ROWSCN Pseudocolumn