How to easily keep versioned data in an Oracle table

Question

I currently have some data stored in a table in an Oracle 12c database. To simplify, it has a K column (the PK) and a D column (some data).

I would like to store versioned data :

• Add a V column to the table, representing a version of the data associated to the key K.
• As such, extend the PK to be (K,V)
• In my application, use a view (in place of the table) that selects (K,V) where ROW_NUMBER() OVER (PARTITION BY K ORDER BY V DESC) = 1
• Upon insert, insert the System Change Number into the V column.

Is the SCN ever-increasing in an Oracle database? Is there a better solution than this?

• Gary: Insufficient information. Integrity constraints would fail or be misleading. Other unique indexes could fail. Anything other than trivial data volumes could lead to poor query executions. The view would be non-updateable. Flashback Data Archive may do what you want. oracle-base.com/articles/12c/…
• Laurent LA RIZZA: @Gary, thank you. In that case, the table would have only the latest data available, along with any expected integrity stuff. That would be nice. What is still unclear is whether there exists a way to create a view over data in the FBA, in order for the application to be able to establish an audit trail of a specific row ?
• William Robertson: Inline versioning also makes foreign keys tricky or impossible, and joins end up being Cartesian products followed by a filter, e.g. you join 3 tables with 5 qualifying versioned rows each and get 125 rows, then filter to get the one row that falls in the desired date range. Ideally you's somehow flag the current row so queries can just look at that, but there's no easy way to do that. I'd prefer to keep only the latest and store previous versions in a separate table, if I can.

Answer 1

You approach does not provide a sensible solution for the case that a K, D pair needs to be deleted.

Instead check "Developing Time-oriented Database Applications in SQL" by Richard T. Snodgrass (2000) (PDF link) and have a look at the literature of "temporal databases":

The main idea of this line of research is to add at least two columns to your database table, which are called start and end in which you store the timestamps, when you added the record (start) and when the record was either deleted or superseded (end). When a record is superseded, a new record is added to the table like this:

K	D	start	end
k	d	2024年12月01日	2024年12月03日
k	b	2024年12月03日	2024年12月12日
...

Using a "point (in time) query", you can then select the version of the data valid at a certain point in time:

Select * 
 from table 
 where to_date('2024-12-04') between start and end 
 and to_date('2024-12-04') != start

(make these intervals either left or right open.)

Using a technique called "coalescing" you can get correct results of the history of some records, even if you leave out other columns (projection). That means for the case, when you only select a subset of the existing columns.

select k, /* no d, */ ... as start, ... as end
 from ... 

K	start	end
k	2024年12月01日	2024年12月12日
...

You can get statements for an Oracle database (as current in the year 2000) from [Snodgrass], see above.

In "Efficient Temporal Coalescing Query Support in Relational Database Systems", Xin Zhou, Fusheng Wang, and Carlo Zaniolo (2006), LNCS 4080, pp. 676--686. you'll find a version of the coalecing statement that uses SQL:2003 OLAP Functions, for which there's support in Oracle. This is a solution, that allows to find the result of a coalescing query with a single scan of the database table.

This technique takes care of deleted records.

Hope this helps someone.

Answer 2

For Oracle, the Flashback Data Archive feature supports requirements like this. Given that you have a system that currently has current-versions only, I presume that most queries would use current-versions only, and the need for historical data would be infrequent. See https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/flashback.html#GUID-06AA782A-3F79-4235-86D5-2D136485F093 for starters.