Join Indexes on Tables with Transaction Time - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

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.