Row Partitioning ANSI Valid-Time Tables - Analytics Database - Teradata Vantage

ANSI 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
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢

Temporal tables should be row partitioned to improve query performance. Partitioning can logically group the table rows into current and history rows. Queries of current rows are directed automatically to the partition containing the current rows.

Column partitioning can also be applied to temporal tables, however the row partitioning described here should always constitute one of the partitioning types used for a temporal table.

Example: Row Partitioning an ANSI Valid-Time Table

To row partition a valid-time table, use the following PARTITION BY clause.

CREATE MULTISET TABLE employee_vt (
       eid INTEGER NOT NULL,
       ename VARCHAR(5) NOT NULL,
       terms VARCHAR(5),
       job_start DATE NOT NULL,
       job_end DATE NOT NULL,
       PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME
   ) PRIMARY INDEX(eid)
   PARTITION BY
     CASE_N(
      END(job_dur)>= CURRENT_DATE AT INTERVAL - 12:59' HOUR TO MINUTE,
                   NO CASE);
The partitioning expression could have used job_end instead of END(job_dur).

Maintaining a Current Partition

As time passes, and current rows become history rows, you should periodically use the ALTER TABLE TO CURRENT statement to transition 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 example:

ALTER TABLE  temporal_table_name  TO CURRENT;

This statement also updates any system-defined join indexes that were automatically created for primary key and unique constraints defined on the table.