We are planning to have a large number of tables enabled for OWM versioning, and we are trying to work through some of the consequences of that.
Does this seem to be true:
As history accumulates for versioned tables in the LIVE workspace, our only options for reducing the space consumed by this history are:
- Disabling versioning for individual tables (which loses all accumulated history)
- Deleting savepoints/compressing the LIVE workspace, which affects all versioned tables simultaneously. Our ability to do this is constrained by the oldest savepoint in the entire database that we still need (whether for DIFF processing or for research availability).
- We can not plan to have 60-day retention for some tables, 30-day retention for other tables, 90-day for others, etc.
- We can not selectively reduce the history for a specific versioned table, if it has grown too much; we can only remove all of its history, or reduce the retained history for all versioned tables together.
. . . .You could implement a table-specific N-day policy by deleting from the underlying table where createtime is NOW-N.
. . . .Modifying the underlying table is not recommended practice.
. . . .Yes, by underlying table I meant the _LT table.
. . . .Re alignment with approved practice: Wait for other users to chime in as well, but I agree with your first bulleted list. I don't accept the conclusions, however. You could acheive a table-specific retention policy if you had a seperate workspace for each table and created savepoints every N-days. (Your software will have to change context depending on which table it's modifying.)
It is not just a matter of switching to the desired workspace before executing the DML. If we have scattered our updates etc. across multiple workspaces then we can not query them together.
If we did 'everything' in child workspaces configured like that, we would have to find some boundaries that would allow us to keep the related updates together, without turining into 'LIVE under a different name'. And we need to understand the impact of keeping those child workspaces around for a much longer time than we had anticipated.
Re querying across multiple contexts
. . . .Setting context inside a query - linear and reliable?, a user proposed changing context via a function that can be used in the WHERE clause. For querying purposes, perhaps you could define views for each table that utilize such a function to go into the table-specific-workspace. Your queries would be written in terms of these views, which would make context switching transparent.
Re keeping related updates together
. . . .You could add a TransactionID column to all tables.
Edited by: Noel Khan on Aug 19, 2011 4:28 PM
Just wanted to confirm that deleting or compressing a workspace would affect all versioned tables. A table based compress operation does not currently exist.
However, 184.108.40.206 added a PurgeTable API which might satisfy your requirement. It allows you to remove historical rows from the _LT table and optionally insert them into an archive table. They cannot however be reinserted into the table at a later time as historical records.