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_TO to store the interval and
we decided that an unknown end time for the validity interval is implemented
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
SELECT * FROM tab WHERE valid_from <= systimestamp AND lnnvl(valid_to <= systimestamp);
- Queries do not need the additional
ORoperator 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.
- It seems a mostly unknown function, so it may not make it easy for other developers looking at your code.
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.