CREATE TABLE (ANSI Valid-Time Table Form)

Purpose

Create a new ANSI valid-time table.

Syntax

Syntax Element	Description
table_name	The name of the valid-time table. May include database qualifier.
valid_start	The name of the column that will store the beginning bound of the valid-time period.
valid_end	The name of the column that will store the ending bound of the valid-time period.
precision	The precision of the timestamp value. The default is 6.
valid_time_period	The name of the valid-time derived period column.
[AS] VALIDTIME	Required to create valid-time tables in Teradata Database. The [AS] VALIDTIME clause allows SELECT statements on valid-time tables to use special temporal qualifiers (AS OF, BETWEEN...AND, FROM...TO, CONTAINED IN), which ANSI does not support on valid-time tables. For more information see “Querying ANSI Valid-Time Tables” on page 64. Note: [AS] VALIDTIME is a Teradata Database extension to ANSI.

ANSI Compliance

This is ANSI SQL:2011 compliant, but includes non-ANSI Teradata Database extensions.

[AS] VALIDTIME is not ANSI compliant, but must be included in valid-time tables defined in Teradata Database.

Usage Notes

Valid time is the Teradata implementation of what ANSI calls “application time.”

The valid_start and valid_end columns must be defined as NOT NULL.

The data types of the valid_start and valid_end columns must match.

To function as a temporal table, the valid-time table must be defined AS VALIDTIME. System-time tables do not have valid time.

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

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

Statistics cannot be collected on the valid-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 an ANSI Valid-Time Table

The following example creates a valid-time table.

CREATE MULTISET TABLE employee_valid_time (

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

Row Partitioning ANSI Valid-Time Tables

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.

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

Note: The partitioning expression could have used job_end instead of END(job_dur).

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.

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