The following table shows the types of join index that can be created on transaction-time tables, and whether the transaction-time column (or component columns of a derived period transaction-time column) must be projected in the index.
| Qualifier | Single Table JI | Multitable JI | Transaction-time Column Required in JI and SEQUENCED VT AJI |
|---|---|---|---|
| CURRENT TRANSACTIONTIME | Allowed | Allowed | Yes / No |
| TRANSACTIONTIME AS OF | Disallowed | Disallowed | Not applicable |
| SEQUENCED TRANSACTIONTIME | Disallowed | Disallowed | Not applicable |
| NONSEQUENCED TRANSACTIONTIME | Allowed | Allowed | No / No |
If no explicit transaction-time qualifier is specified in the statement, the system uses the session transaction-time qualifier.
When a current join index is created, the following condition is added to the join definition:END(< TransactionTimeColumn >) IS UNTIL_CLOSED.
If a current join index on a transaction-time table has an outer join and results in a derived table, Teradata Database returns an error.
Teradata Database maintains any current join indexes in the transaction-time dimension with every DML statement on the base table.
Although projecting the transaction-time column is not required for nonsequenced join indexes, doing so can increase the usefulness of the index.