Jul
29
2013 Posted by Mark Gillis

Time Travel Gotchas Part 1

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 

 

« | »
Have a Question?

Get in touch with our expert team and see how we can help with your IT project, call us on +44(0) 870 2411 550 or use our contact form…