15.00 - Join Index Updates Are Maintained by Teradata Database - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Join Index Updates Are Maintained by Teradata Database

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. See “Join Indexes, Expressions, and Built-In Functions” on page 372, “ALTER TABLE TO CURRENT” on page 140 for more information about updatable date and timestamp partitioning functions.