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.