17.05 - Join Indexes on Tables with Transaction 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 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.