Developing Time-Oriented Database Applications in SQL
An enjoyable plunge into the how-to of time and data, with real-world situations and code.
TABLE OF CONTENTS Developing Time-Oriented Database Applications in SQL
Chapter 9: Transaction-Time State Tables
OVERVIEW
A transaction-time state table associates with each row the period of time that row was present in the monitored table, thereby allowing the state of the monitored table at any previous point in time to be reconstructed. A state table is more amenable to sequenced and nonsequenced queries than is a tracking log.
Different organizations for state tables selectively minimize space overhead, modification time, degree of legacy code change required, and query complexity. Sometimes triggers can be used to maintain the state table.
If a state table becomes too large, the DBA can vacuum it in a disciplined fashion.
The previous chapter addressed Nigel's problem of spurious changes in the PROJECTIONS table by defining an associated tracking log that captures these changes for later perusal. Several organizations of the tracking log were examined: recording before-images, recording after-images, recording both before-and after-images, and recording the actual modification operations (termed a "backlog"). These variants all utilized a single transaction timestamp, When_Changed. To realize some queries, in particular sequenced queries, we recommended that the tracking log be converted into a transaction-time state table, with period timestamping.
In this chapter, we adopt exactly the opposite approach. We maintain the transaction-time state table directly, with the monitored (snapshot) table available either as a view or as a regular table.
If space is at a premium, maintaining an instant-stamped tracking log (specifically, the after-image organization) with triggers is the way to go. On the other hand, if sequenced queries or sequenced integrity...
Copyright Morgan Kaufmann Publishers, Inc. 2000 under license agreement with Books24x7
