Modifying Temporal Tables - Teradata Database

Modifications to temporal tables involve special time-aware handing by Teradata Database. Because they are used for different purposes, system-time columns are treated differently than valid-time columns when rows are modified.

Temporal modifications to system-time columns are straightforward and entirely automatic. They are not influenced by the nature of the modification.

When a row is deleted from a system-time table, the row is not physically deleted from the table. The end of the system-time period of the row is set to the time of the deletion, and the row becomes a closed row. It no longer participates in most SQL operations on the table, but remains in the table as a historical record of what existed in the past.

When a row is modified in a system-time table, the end of the system-time period of the row is set to the time of the modification, closing the row, and a copy of the row with the modified data is inserted into the table. The system-time period of the new row begins at the time of the modification, and ends at UNTIL_CLOSED. The new row is active in the database and can participate in SQL operations.

For more information on modifying ANSI system-time tables, see “Modifying Rows in ANSI System-Time Tables” on page 42.

Assume a table represents contracts, with each row presenting the terms of an existing contract. If the terms of the contract are changed during the contract period, a regular update to a nontemporal table would result in the table reflecting the new contract terms. But potentially useful information would be lost: the fact that, prior to the modification, the contract terms were different. Valid-time tables let you model this reality using special temporal syntax for modifications, and by special handling of the rows changed by these modifications.

Modifications to valid-time tables are more flexible than those to system-time tables. Although the database again does the temporal bookkeeping for you automatically, the nature of the automatic modifications depends on the relationship between the valid time of the row and the effective time period you specify for the modification.

When you make UPDATE and DELETE modifications to valid-time tables, you can optionally specify a time period for which the modification applies. This is called the period of applicability (PA) of the modification. In this context, the valid-time period of a row is called the period of validity (PV) of the row. The PA of the modification syntax acts as an additional qualifier on the SQL. It is the relationship between the PA and PV that determines which rows can be modified, and whether the modification automatically creates new rows in the table that allow the database to reflect the time-delimited nature of the change.

The simplest case is a change that is effective as of one point in time during the PV of a row, and that lasts for the remaining duration of the PV. A simple change to terms of a contract is an example of this. This is how Teradata Database handles the change to preserve the temporal nature of change:

A copy of the row is automatically created and modified to show the new terms. The PV of the new row begins at the time of the change, to show when the new terms started. The PV of the row retains the original ending bound for the valid-time column, to retain the original contract end date, because the new terms are valid for the remaining life of the contract.

The original row, storing the original terms of the contract is marked as a history row. The ending bound of the valid-time of the old row is set to the time of the modification, because that is when the old terms in that row ceased to be valid.

Modifications to tables that have a valid-time dimension can apply to any period of time, even times that have passed or that are in the future. The changes affect only those rows with PVs that overlap the PA of the modification, and only for as long as the PA and PV coincide.

For example, if the PA of the modification lies within the PV of a row, such as a change to a contract that starts after the contract has begun, but ends before the contract expires, three rows will result from the modification:

One row has the original information, and a valid-time period that covers the time from the beginning of the original PV of the row until the modification takes effect.

The second row has the modified information, and a valid-time period that matches the PA of the modification statement.

The third row retains the original row information, but has a valid-time period that begins when the modification is no longer valid, and extends through the end time of the original PV of the row.

In this way, valid-time tables keep an automatic history of all changes. Unlike closed rows in system-time tables, history rows in valid-time tables remain accessible to all SQL operations. Because they model the real world, valid-time tables can have rows with a PV in the future, because things like contracts and policies may not begin or end until a future date.

Modifications that do not specify a PA behave just as modifications do on nontemporal tables, without affecting the valid time of the modified rows. They affect all rows in the table that meet the query criteria, regardless of the valid time of rows.

For more information about modifying rows in ANSI valid-time tables, see “Modifying Rows in ANSI Valid-Time Tables” on page 68.

The system-time and valid-time dimensions of bitemporal tables are independent of each other, and are affected just as they are in system-time and valid-time tables, with one important difference. In a bitemporal table only rows that are open in the system-time dimension (those that have a system-time period ending bound of UNTIL_CLOSED) participate in modifications. After a row is closed in system time, it is no longer active in the database.

Because of the system-time dimension, all modifications to rows in bitemporal tables automatically create closed rows in the system-time dimension. This is in addition to rows that might be created to account for changes in the valid-time dimension.

For example, if a row in a bitemporal table is deleted, the ending bound of the system-time period is automatically changed to reflect the time of the deletion, and the row is closed to further modifications. The database reality, reflected by the modified ending bound of the system-time period, is that the row has been deleted.

The valid-time period of the closed row remains unchanged. Because the deletion does not affect the ending bound of the valid-time period, the row information retains its character in the valid-time dimension as it existed at the time of the deletion.

The result of updates to rows in bitemporal tables are more complex, but are completely consistent with the idea of the system-time and valid-time dimensions acting independently.

For example, assume the contract terms are stored in a row of a bitemporal table. If the terms are changed during the period when the contract is valid, the row must be updated. Because this is a temporal table, Teradata Database automatically inserts a copy of the row to store the new terms. The PV of the new row is automatically set to begin at the time of the change, and end at the original end date of the contract. The beginning bound of the system-time period of the new row reflects when the new row was created, and the end of the system-time period is indefinite, set as UNTIL_CLOSED, which it will remain until the newly added row is deleted or modified.

The original row is automatically modified to have the end of the PV reflect the time of the change, when the old contract terms became obsolete. This row becomes a history row in the valid-time dimension. Note that both rows remain open rows in the system- time dimension, and as such, both are still available to all types of DML queries and modifications. These changes are purely a result of the valid-time dimension of the table.

Because the table also includes a system-time dimension, however, a copy is made of the original row, reflecting the original PV, but this row is now closed in the system-time dimension as of the time the contract terms changed. No further changes can be made to this row, because it is closed in system time. It provides a permanent “before” snapshot of the original row as it existed in the database before it was changed.

Note that the temporal operations performed on the row automatically by Teradata Database include independent actions that result from the table having both a system-time dimension and a valid-time dimension.

For more information about modifying ANSI bitemporal tables, see “Modifying Rows in ANSI Bitemporal Tables” on page 92.