Our application built using Oracle 11g requires that we keep historic data.
The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically we need to save some stuff 10 years, and we dig for old data a few times each year. The historical data is also used frequently during development and testing for debugging purposes.
Or homemade solution is to add an extra sequence number to the primary key, inserting a copy with a new seq-no when an existing row is "modified". We are using the latest seq-no as the actual "active" data in all queries. Effectively we are doing a subselect or join on max(seq_no) + the other primkeys whenever we access a table.
id varchar, --pk seq_no number, --pk omg_ponies varchar,
The performance is ...almost adequate, but we are seeing bigger data volumes, and a lot of queries are getting unbelievably complex as our application grows. It also hinders us from using JPA or Hibernate efficiently as the model gets messed up. We are solving that with views, but it has it's complications.
So how to handle historization? Oracle seems to recommend using Flashback and it looks like a Good Thing.
But how do we migrate, keeping our (10 year long) history? Ideally, only one row should be left for each
id, with the old variants (lower seq_no:s) available in the flashback logs. Preferably with an proper "old" timestamp.
Furthermore, how do you handle refactoring? We see a lot of new business requirements, and occasionally we need to do major changes, like splitting a table into two, or other larger modifications caused by new requirements and features. Until now, we've been doing larger changes once or twice each year. How do we handle changes, while retaining or repopulating the history, again preferably with the proper "older" dates.
Footnotes: We do not believe that we must leave historic entries "intact". It's OK to modify the logical model as long as we are not loosing track of changes and certain events.