17.10 - Bitemporal Table Modifications - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)

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, Vantage 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 Vantage 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.