17.10 - Join Index Updates Are Maintained by Vantage - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Most values of a join index are automatically maintained by Vantage 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.