17.05 - Join Indexes on Tables with Valid Time - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

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
NONSEQUENCED VALIDTIME Allowed Allowed No / No

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

Teradata Database 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.

Example: Join index on table with valid time

CREATE JOIN INDEX AS
SEQUENCED VALIDTIME and CURRENT TRANSACTIONTIME
SELECT X1, Y1, VT1, TT1, X2, Y2, VT2, TT2
FROM   t1, t2
WHERE  END(t1.VT1) >= TEMPORAL_DATE
AND    END(t2.VT2) >= TEMPORAL_DATE;