17.00 - Row Partitioning ANSI System-Time Tables - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Temporal tables should be row partitioned to improve query performance. Partitioning can logically group the table rows into open and closed rows. Queries of open rows are directed automatically to the partition containing the open 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 System-Time Table

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

CREATE MULTISET TABLE employee_systime (
       eid INTEGER NOT NULL,
       ename VARCHAR(10) NOT NULL,
       deptno INTEGER NOT NULL,
       sys_start TIMESTAMP WITH TIME ZONE NOT NULL
                              GENERATED ALWAYS AS ROW START,
       sys_end TIMESTAMP WITH TIME ZONE NOT NULL
                            GENERATED ALWAYS AS ROW END,
       PERIOD FOR SYSTEM_TIME(sys_start, sys_end)
   ) PRIMARY INDEX(eid) WITH SYSTEM VERSIONING
   PARTITION BY
      CASE_N (END(SYSTEM_TIME) >= CURRENT_TIMESTAMP, NO CASE);

The partitioning expression could have used sys_end instead of END(SYSTEM_TIME).

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.