Archiving Temporal Tables | ANSI Temporal Table Support | Teradata Vantage - 17.10 - Archiving Temporal Tables - 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)
Archive operations on temporal tables use the same syntax as nontemporal tables. For temporal tables, the ARCHIVE, RESTORE, and COPY commands can operate on:
  • Entire temporal tables

    Archiving an entire temporal table saves all rows to the archive, including history, current, future, open, and closed rows.

  • Specified partitions of a temporal table
    An archive operation can be limited to specified partitions of row-partitioned temporal tables with primary indexes, provided those tables are not also column partitioned. For example, a bitemporal table that is partitioned using the recommended partitioning expression has rows separated into the following partitions:
    • open rows with valid-time periods that are current and future
    • open rows with valid-time periods that are history
    • closed rows

    The archive can be limited to store only the current and history open rows from the first partition.

Use the following guidelines when archiving temporal tables that have been partitioned into current and history rows:
  • History rows are automatically formed in partitions containing current and future rows, and in partitions containing open rows when current, open rows are modified. The ALTER TABLE TO CURRENT statement repartitions the table, moving history rows out of the current partition.

    If archiving the current and future rows, ensure the current partition includes only current and future rows by issuing an ALTER TABLE TO CURRENT statement on the table immediately prior to the ARCHIVE operation.

    If restoring only current and future rows to an existing temporal table, issue an ALTER TABLE TO CURRENT statement on the table immediately prior to the restore operation.

  • Archive the complete partition that isolates the open current and future rows, or archive the entire table. Do not archive a history partition alone, or a subset of the partition for open current and future rows.
  • RESTORE the entire temporal archive. Never restore only a portion of the archive.
  • If only the current partition is restored for a temporal table, the existing history partition for that table is deleted, and a new history is begun starting from the time of the restore. This loses historical information from the existing table.

    Teradata recommends a dual archive strategy for temporal tables. Save entire temporal tables in one archive, and the current temporal partitions in a separate archive. The archive containing entire tables can be used to restore temporal tables including history information. The archive containing current partitions can be used for disaster recovery, when restoring history rows is not desired.

Related Information

For Information on... See...
Partitioning temporal tables
ALTER TABLE TO CURRENT