Time Travel query offers a quick and intuitive way of querying your data for historical scenarios, as well as the traditional current picture. There are a number of good articles out there showing how to enable the feature and some suggestions on how to use it. But like nearly every option, it’s got pitfalls and overheads that will catch you out. I’m going to share a few that I’ve come across, specifically with reference to System Time. Business time has much less overhead than System Time, as there is no History table to be concerned about.
Many of the articles I’ve read and some of the instructions in the manuals suggest using the
CREATE TABLE history table LIKE base table
command to ensure you get a history table with the required matching attributes that will allow you to enable versioning. The problem with this is that the history table will be very simple and will not be optimized for the access or maintenance.
Unrestricted Growth
The trouble with storing historical data is that it will drastically increase the footprint of your data. Without invoking SYSTEM TIME versioning on your base table, the action of updating a row doesn’t change the amount of data stored (ignoring the overhead of VARCHAR columns for the sake of argument) and deleting a row will reduce the amount of data. Before you enable SYSTEM TIME versioning, the actions of inserting, updating and deleting a single row would result in a 0 increase in data footprint. Once you have enabled SYSTEM TIME versioning, the same set of actions would leave you with 2 rows still in existence, stored in the history table. As a simple illustration (with an extra UPDATE for the hell of it) it will go like this:
Action Rows in Base table Rows in History table
INSERT 1 0
UPDATE 1 1
UPDATE 1 2
DELETE 0 3
The only action that won’t populate the History table is the original insert so you can see that a table with a high-volume of Update activity is going to generate a lot of data in the history table.
And it will continue to grow until there is a mechanism or maintenance job implemented to reduce it. Regular scheduled DELETE jobs are OK if you’re dealing with fairly low-volumes and can make use of a low activity window in your application schedule. But if you’re anticipating higher-volumes and a more limited, or non-existent, maintenance window then defining your history table with Partitioning might be the answer. There’s no reason that the History table shouldn’t be organized in a completely different way to the Base table as long as the column attributes are identical. I recently set up a History table with the following options:
PARTITION BY RANGE( SYSTEM_END )
( STARTING ‘2011-01-01-01.01.01.000000’
ENDING ‘2014-01-01-01.01.01.000000’
EVERY 3 MONTH
)
Organize by Dimensions
( SYSTEM_BEGIN_YEAR )
which has given us the benefit of being able to detach and add partitions as needed. By using DETACH we can roll-out a section of the oldest data without impacting the on-going activity on the base table and can also ADD in a new partition to service the anticipated versioning for the future, with no performance impact either.
ALTER TABLE CONTRACT_HISTORY_MDC DETACH PARTITION PART0 INTO CONTRACT_HISTORY_PART0
ALTER TABLE CONTRACT_HISTORY_MDC ADD PARTITION STARTING FROM ‘2014-01-01-01.01.01.000000000000’ inclusive ENDING AT ‘2014-04-01’ exclusive
See Time Travel Gotchas Part Two