CREATE TABLE (ANSI Bitemporal Table Form) - Teradata Database

Purpose  

Create a new ANSI bitemporal table.

Syntax  

There is no special temporal syntax for creating bitemporal tables. It simply combines the forms discussed in “CREATE TABLE (ANSI System-Time Table Form)” on page 28 and “CREATE TABLE (ANSI Valid-Time Table Form)” on page 52. Note that a bitemporal table also combines the rules, restrictions, and usage notes listed for both system-time and valid-time tables..

For example, a bitemporal table cannot be the source table for a CREATE TABLE ... AS statement, and statistics cannot be collected on the derived period columns of bitemporal tables.

Note: All restrictions for system-time and valid-time columns described in Chapter 3: “Working With ANSI System-Time Tables” and Chapter 4: “Working With ANSI Valid-Time Tables” apply to bitemporal tables.

Example : Creating an ANSI Bitemporal Table

The following example creates an ANSI bitemporal table.

CREATE MULTISET TABLE employee_bitemporal (

 eid INTEGER NOT NULL,

 ename VARCHAR(5),

 deptno INTEGER 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,

 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 Bitemporal Tables

Temporal tables should be row partitioned to improve query performance. Partitioning can logically group the table rows into current and history, open and closed rows. For bitemporal tables, queries of current and open rows are directed automatically to the partition containing these 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_bitemp (

 eid INTEGER NOT NULL,

 ename VARCHAR(5),

 deptno INTEGER 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,

 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) 

 PARTITION BY CASE_N (

  (END(job_dur) >= CURRENT_DATE AT INTERVAL -'12:59' HOUR TO MINUTE) 

    AND END(SYSTEM_TIME) >= CURRENT_TIMESTAMP,

  END(job_dur) < CURRENT_DATE AT INTERVAL -'12:59' HOUR TO MINUTE 

   AND END(SYSTEM_TIME) >= CURRENT_TIMESTAMP,

  END(SYSTEM_TIME) < CURRENT_TIMESTAMP

  )
WITH SYSTEM VERSIONING;

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

WITH SYSTEM VERSIONING must be the last clause in the CREATE TABLE statement.

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.