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?

  • 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/…
    – Gary
    May 28 at 10:53
  • @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 ? May 31 at 7:56
  • 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. Jun 1 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.