15.00 - CREATE JOIN INDEX (Temporal Form) - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

CREATE JOIN INDEX (Temporal Form)

Purpose  

Creates a join index on temporal tables.

For more information on sequenced aggregate join indexes, see “Aggregate Functions in Sequenced Queries” on page 150.

Syntax  

Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

 

Syntax Element …

Specifies …

database_name

an optional database name or user name specified if the join index is to be contained in a database or user other than the current database or user.

user_name

join_index_name

the name given to the join index created by this statement.

FALLBACK [PROTECTION]

that the join index uses fallback protection.

NO FALLBACK [PROTECTION]

that the join index does not use fallback protection. This is the default.

CHECKSUM = integrity_checking_level

a table-specific disk I/O integrity checksum level.

CURRENT VALIDTIME

that the join index is current in valid time.

The select_statement must project the valid-time column in a join index definition that is current in valid time. If the column is a derived period column, the component columns of the derived period column must be projected.

SEQUENCED VALIDTIME

that the join index is sequenced in valid time.

The select_statement must project the valid-time column in a join index definition that is sequenced in valid time. If the column is a derived period column, the component columns of the derived period column must be projected.

NONSEQUENCED VALIDTIME

that the join index is nonsequenced in valid time.

AND

a keyword for specifying both a valid-time qualifier and a transaction-time qualifier. The valid-time and transaction-time qualifiers, if used together, can be specified in any order.

CURRENT TRANSACTIONTIME

that the join index is current in transaction time.

The select_statement must project the transaction-time column in a join index definition that is current in transaction time. If the column is a derived period column, the component columns of the derived period column must be projected.

NONSEQUENCED TRANSACTIONTIME

that the join index is nonsequenced in transaction time.

select_statement

conventional SELECT statement syntax for creating a join index.

Join Indexes on Tables with Transaction Time

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.

Join Indexes on Tables with Valid Time

The following table shows the types of join index that can be created on valid-time tables, and whether the valid-time column (or component columns of a derived period valid-time column) must be projected in the index.

 

Qualifier

Single Table JI

Multitable JI

Valid-time Column Required in
JI / Aggregate JI

CURRENT VALIDTIME

Allowed

Disallowed

Yes / No

VALIDTIME AS OF

Disallowed

Disallowed

Not applicable

SEQUENCED VALIDTIME

Allowed

Allowed

Yes / No

NONSEQUENCED VALIDTIME

Allowed

Allowed

No / No

If no explicit valid-time qualifier is specified in the statement, the system uses the session valid-time qualifier.

Teradata Database maintains any current and sequenced join indexes in the valid-time dimension with every current or sequenced DML statement on the base table, regardless of whether the column being modified is included in the join index.

For sequenced join indexes, the system does not append a VALIDTIME column that is normally added to the results of a SEQUENCED SELECT statement.

Although projecting the valid-time column is not required for nonsequenced join indexes, doing so can increase the usefulness of the index.

To avoid the high current join index and sequenced join index maintenance cost for a table with valid time, modify the columns that are not referenced in the current join index using nonsequenced DML (nontemporal DML if table is bitemporal). Such columns must be time-invariant columns whose history is not required.

If the join index involves only time-invariant columns, the best practice is to create a nonsequenced join index. This avoids the reference of a valid-time column and, thus, avoids join index maintenance steps when columns that are not part of the join index are modified.

Current multitable JIs are not supported for valid-time tables, however a sequenced valid-time index can be created to include only current valid-time queries issued at any point in time from current time to future time, as shown in the following example of a multitable join index created from two bitemporal tables. Note that the temporal columns must be projected in the sequenced index.

Example  

CREATE JOIN INDEX AS 
SEQUENCED VALIDTIME and CURRENT TRANSACTIONTIME
SELECT X1, Y1, VT1, TT1, X2, Y2, VT2, TT2
FROM   t1, t2
WHERE  END(t1.VT1) >= TEMPORAL_DATE
AND    END(t2.VT2) >= TEMPORAL_DATE;

TEMPORAL_DATE and TEMPORAL_TIMESTAMP in Join Indexes

When TEMPORAL_DATE and TEMPORAL_TIMESTAMP are used in the WHERE clause of a CREATE JOIN INDEX statement, they resolve to static values. They do not confer any temporal behavior on the join index.

Maintaining Current Join Indexes

As time passes, and current rows become history rows, you should periodically use the ALTER TABLE TO CURRENT statement to ensure that the rows in the index continue to reflect only rows that are valid. For example:

   ALTER TABLE Policy_JI TO CURRENT;

Related Information

 

For more information on...

See...

ALTER TABLE TO CURRENT

SQL Data Definition Language

CREATE JOIN INDEX

SQL Data Definition Language

join indexes for temporal tables

“Creating Join Indexes for Temporal Tables” on page 40