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;