CREATE TABLE (ANSI System-Time Table Form) - Teradata Database

Purpose  

Create a new ANSI system-time table.

Syntax  

Syntax Element	Description
table_name	The name of the system-time table. May include database qualifier.
sys_start	The name of the column that will store the beginning bound of the system-time period.
GENERATED ALWAYS AS ROW START	Required attribute for column that defines the beginning bound of system-time period.
sys_end	The name of the column that will store the ending bound of the system-time period.
GENERATED ALWAYS AS ROW END	Required attribute for column that defines the ending bound of system-time period.
PERIOD FOR SYSTEM_TIME	Creates the system-time derived period column.
SYSTEM VERSIONING	Required attribute for system-time temporal tables. Must be the last clause in the CREATE TABLE statement.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Usage Notes  

The sys_start column must be defined as NOT NULL and GENERATED ALWAYS AS ROW START. The sys_end column must be defined as NOT NULL and GENERATED ALWAYS AS ROW END.

The GENERATED ALWAYS AS ROW START or END attributes cannot be dropped from the definitions of the columns that constitute the SYSTEM_TIME derived period column.

Component columns of a system-time derived period column cannot be part of the primary index.

To function as a temporal table, the system-time table must be defined WITH SYSTEM VERSIONING. (Valid-time tables do not have system versioning.)

A table can have only one system-time period definition.

A system-time table cannot be a queue, error, global temporary, global temporary trace, or volatile table.

CHECK constraints on tables with system time cannot include the start or end columns of the system-time period.

The start and end columns of the system-time period cannot be part of a primary or foreign key of a temporal referential constraint.

System-time tables cannot act as source tables in CREATE TABLE AS statements.

Statistics cannot be collected on the system-time derived period column, but they can be collected on the component start and end time columns.

Algorithmic compression (ALC) is not allowed on DateTime columns that act as the beginning and ending bound values of a temporal derived period column.

Example : Creating a System-Time Table

The following example creates a system-time table and includes the system versioning clause, which is required to make the table a temporal table.

CREATE MULTISET TABLE employee_system_time (

 eid INTEGER NOT NULL,

 name VARCHAR(10)NOT NULL,

 deptno INTEGER NOT NULL,

 sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
           GENERATED ALWAYS AS ROW START,

 sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
           GENERATED ALWAYS AS ROW END,

 PERIOD FOR SYSTEM_TIME(sys_start, sys_end)
 )

 PRIMARY INDEX (eid) WITH SYSTEM VERSIONING;

Row Partitioning ANSI System-Time Tables

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.

Note: 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);

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

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.