LNNVL() and Time Oriented Data

LNNVL() is one of the Oracle SQL functions I had never used before. Just recently I had to look at queries for a valid-time state table and realized that the function can be used as an alternative to the way I wrote queries for such tables before.

Richard T. Snodgrass describes valid-time state tables in his book “Developing Time-Oriented Database Applications in SQL”. Such a table contains not only the current state but also the complete history. This can be implemented by adding additional columns to the table which describe the time interval when the row is or has been valid. We use the columns VALID_FROM and VALID_TO to store the interval and we decided that an unknown end time for the validity interval is implemented as NULL.

In our implementation we use the closed-open representation as introduced in chapter four of the book. This allows us to use the same time value as VALID_TO for the old state and VALID_FROM for the new state. It ensures that there is no gap between the two intervals even when the timestamp data type with higher precision is used. So the general query to fetch all currently valid rows looks like this:

SELECT *
  FROM tab
 WHERE valid_from <= systimestamp
   AND (valid_to > systimestamp OR valid_to IS NULL);

With the Oracle function LNNVL() the query can be written like this:

SELECT *
  FROM tab
 WHERE valid_from <= systimestamp
   AND lnnvl(valid_to <= systimestamp);

Advantages

  • Queries do not need the additional OR operator any more.
  • Both terms use the same <= operator. This prevents the developer from constantly mixing up the open and the closed side of the interval.

Disadvantages

  • It seems a mostly unknown function, so it may not make it easy for other developers looking at your code.
  • The lnnvl() function can’t be used in a function based indexe, so this isn’t a new way to improve the query access path.

The book “Developing Time-Oriented Database Applications in SQL” is freely available for download on the website of Richard T. Snodgrass.