16.20 - Join Index Updates Are Maintained by Teradata Database - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

Most values of a join index are automatically maintained by Teradata Database when update operations (DELETE, INSERT, MERGE, UPDATE) are performed on columns in their underlying base tables that are also defined for the join index. Additional steps are included in the execution plan to regenerate the affected portion of the stored join result.

The exception to this update rule is the values for any join index partitioning expression that specifies a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP built-in function. In this case, you must submit an appropriate ALTER TABLE TO CURRENT request to reconcile the values stored for the date or timestamp for the partitioning expression. Note that you cannot use an ALTER TABLE TO CURRENT request to reconcile a CURRENT_TIME function that is specified within a partitioning expression.

Similarly, you cannot reconcile the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP values specified in the WHERE clause of a join index. For more information about updatable date and timestamp partitioning functions, see Join Indexes, Expressions, and Built-In Functions and ALTER TABLE TO CURRENT.