15.00 - Creating Join Indexes for Temporal Tables - Teradata Database

Teradata Database Temporal Table Support

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

Creating Join Indexes for Temporal Tables

Join indexes aid performance by providing a smaller data set and shorter data access path for common queries that would otherwise require full table scans. Join indexes can also help the Optimizer better optimize queries. Join indexes can be created for temporal tables.

Creating a Join Index on a Table with Valid Time

To create a join index on a table with valid time, precede the SELECT statement in the join index definition with a CURRENT VALIDTIME, NONSEQUENCED VALIDTIME, or SEQUENCED VALIDTIME qualifier. For example:

   CREATE JOIN INDEX Policy_JI AS 
   CURRENT VALIDTIME SELECT Policy_ID, Policy_Type, Validity 
   FROM Policy;

A join index that is current or sequenced in the valid-time dimension must project the valid-time column in the SELECT statement to ensure that the join index is used appropriately. Here, the Validity column is the valid-time column.

Creating a Join Index on a Table with Transaction Time

To create a join index on a table with transaction time, precede the SELECT statement in the join index definition with a CURRENT TRANSACTIONTIME or NONSEQUENCED TRANSACTIONTIME qualifier. For example:

   CREATE JOIN INDEX Policy_Types_JI AS 
   CURRENT TRANSACTIONTIME SELECT Policy_Type, Policy_Duration
   FROM Policy_Types;

A join index that is current in the transaction-time dimension must project the transaction-time column in the SELECT statement. Here, the Policy_Duration column is the transaction-time column.

Maintaining Current Join Indexes

As time passes, and current rows become history rows, you should periodically use the ALTER TABLE TO CURRENT statement to ensure that the rows in the index continue to reflect only rows that are valid. For example:

   ALTER TABLE Policy_JI TO CURRENT;

Related Information

 

For more information on...

See...

CREATE JOIN INDEX (temporal form)

“CREATE JOIN INDEX (Temporal Form)” on page 57

ALTER TABLE TO CURRENT (regular form)

SQL Data Definition Language