15.00 - Row Partitioning Temporal Tables - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Row Partitioning Temporal Tables

To improve the performance of current queries on a temporal table, the table should be row partitioned. The table is logically divided into a set of current rows, and history rows (open rows and closed rows in the transaction-time dimension). Current queries are directed automatically to the partition containing current, 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.

Row Partitioning a Valid-Time Table

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

Example  

   CREATE MULTISET TABLE Policy(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Validity PERIOD(DATE) NOT NULL AS VALIDTIME
      )
   PRIMARY INDEX(Policy_ID)
   PARTITION BY 
     CASE_N(
      END(Validity)>= CURRENT_DATE AT INTERVAL - 12:59' HOUR TO MINUTE,
                   NO CASE);

Row Partitioning a Transaction-Time Table

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

Example  

   CREATE MULTISET TABLE Policy_Types (
      Policy_Name VARCHAR(20),
      Policy_Type CHAR(2) NOT NULL PRIMARY KEY,
      Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL 
         AS TRANSACTIONTIME
      )
   PRIMARY INDEX (Policy_Name)
   PARTITION BY 
      CASE_N (END(Policy_Duration) >= CURRENT_TIMESTAMP, NO CASE);

Row Partitioning a Bitemporal Table

To row partition a bitemporal table, use the following PARTITION BY clause.

Example  

   CREATE MULTISET TABLE Policy_Bitemp (
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Validity PERIOD(DATE) NOT NULL AS VALIDTIME,
      Policy_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
         AS TRANSACTIONTIME
      )
   PRIMARY INDEX (Policy_ID)
   PARTITION BY CASE_N(
      (END(Validity) IS NULL OR
       END(Validity) >= CURRENT_DATE AT
                        INTERVAL -'12:59' HOUR TO MINUTE) AND
       END(Policy_Duration) >= CURRENT_TIMESTAMP,
       END(Validity) < CURRENT_DATE AT 
                       INTERVAL -'12:59' HOUR TO MINUTE AND
       END(Policy_Duration) >= CURRENT_TIMESTAMP,
       END(Policy_Duration) < CURRENT_TIMESTAMP);

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 Policy 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.

Related Information

 

For more information on...

See...

row partitioning temporal tables

“Partitioning Expressions for Temporal Tables” on page 78

CREATE TABLE (temporal form)

“CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65

ALTER TABLE TO CURRENT (regular form)

SQL Data Definition Language