17.10 - Join Indexes on Tables with Transaction Time - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

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, the system returns an error.

Vantage 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.