17.10 - Join Indexes on Tables with Valid Time - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

The following table shows the types of join index that can be created on valid-time tables, and whether the valid-time column (or component columns of a derived period valid-time column) must be projected in the index.

Qualifier Single Table JI Multitable JI Valid-time Column Required in JI / Aggregate JI
CURRENT VALIDTIME Allowed Disallowed Yes / No
VALIDTIME AS OF Disallowed Disallowed Not applicable
SEQUENCED VALIDTIME Allowed Allowed Yes / No

If no explicit valid-time qualifier is specified in the statement, the system uses the session valid-time qualifier.

Vantage maintains any current and sequenced join indexes in the valid-time dimension with every current or sequenced DML statement on the base table, regardless of whether the column being modified is included in the join index.

For sequenced join indexes, the system does not append a VALIDTIME column that is normally added to the results of a SEQUENCED SELECT statement.

Although projecting the valid-time column is not required for nonsequenced join indexes, doing so can increase the usefulness of the index.

To avoid the high current join index and sequenced join index maintenance cost for a table with valid time, modify the columns that are not referenced in the current join index using nonsequenced DML (nontemporal DML if table is bitemporal). Such columns must be time-invariant columns whose history is not required.

If the join index involves only time-invariant columns, the best practice is to create a nonsequenced join index. This avoids the reference of a valid-time column and, thus, avoids join index maintenance steps when columns that are not part of the join index are modified.

Current multitable JIs are not supported for valid-time tables, however a sequenced valid-time index can be created to include only current valid-time queries issued at any point in time from current time to future time, as shown in the following example of a multitable join index created from two bitemporal tables. Note that the temporal columns must be projected in the sequenced index.