Partitioning Expressions for Temporal Tables | Teradata Vantage - Partitioning Expressions for Temporal Tables - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™
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 of the following table type 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.