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
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
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;
For more information on...
CREATE JOIN INDEX (temporal form)
ALTER TABLE TO CURRENT (regular form)
SQL Data Definition Language