Partitioning Expressions for Temporal Tables | Teradata Vantage - 17.10 - Partitioning Expressions for Temporal Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)
To improve the performance of current queries on a temporal table, the table should be row partitioned:
  • For tables with valid-time columns, the partitions logically separate the current and future rows from the history rows, so fewer rows need to be scanned for current queries.
  • For tables with transaction-time columns, the partitions logically separate the open rows from the closed rows.
  • For bitemporal tables, the partitions separate open current and future rows from open history rows from all closed rows.
Like other types of tables, temporal tables can have several levels of partitioning defined, including column partitioning. The temporal row partitioning described here should be one of those levels. For more information on column partitioning, see Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

The recommended partitioning expressions discussed for each table type below reference the built-in functions CURRENT_DATE, CURRENT_TIMESTAMP, or both.

As time passes, the values of CURRENT_DATE and CURRENT_TIMESTAMP differ from the values that were used to resolve CURRENT_DATE and CURRENT_TIMESTAMP in the partitioning expressions when the table was created. Because of this, the partition intended to hold the current and future rows might include rows that are not strictly current. The optimizer can nevertheless successfully find current rows within the partition. The presence of some history rows in the current partition will not adversely affect performance unless there are a great many history rows there.

Use the ALTER TABLE TO CURRENT statement periodically to move history rows out of the current partition into the history partition. ALTER TABLE TO CURRENT resolves the partitioning expressions again, transitioning rows to their appropriate partitions per the updated partitioning expressions. For more information on ALTER TABLE TO CURRENT, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

To ensure optimal row partition elimination, the granularity of the time specification in AS OF and SEQUENCED queries on row partitioned temporal tables should be no finer than the valid-time column in the query with the coarsest time granularity.