17.05 - Deferred Deletion and Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
Vantage uses deferred partition deletion on row-partitioned base tables and row-partitioned join indexes for the following scenarios:
  • Row-partitioned tables.
  • Row-partitioned join indexes

Deletion and Row-Partitioned Join Indexes

If you attempt to delete a row from a base table that causes an insert into a row-partitioned join index or an update of an index row in a row-partitioned join index such that:
  • any of the partitioning expressions for that join index row evaluate to null, or
  • the partitioning expression is an expression that is not CASE_N or RANGE_N, it's result is not between 1 and 65535 for the row

the system aborts the request (ANSI mode) or transaction (Teradata mode). It does not perform the insert or update and returns an error or failure, respectively.

Deleting a row from a base table does not always cause the deletion of a join index on that base table. For example, you can specify a WHERE clause in the CREATE JOIN INDEX statement to create a sparse join index for which only those rows that meet the condition of the WHERE clause are inserted into the index, or, for the case of a row in the join index being updated in such a way that it no longer meets the conditions of the WHERE clause after the update, cause that row to be deleted from the index.

The process for this activity is as follows:

  1. The system checks the WHERE clause condition for its truth value after the update to the row.
    Condition Description
    FALSE the system deletes the row from the sparse join index.
    TRUE the system retains the row in the sparse join index and proceeds to stage b.
  2. The system evaluates the new result of the partitioning expression for the updated row.
    Partitioning Expression Description
    • Evaluates to null, or
    • Expression is not CASE_N or RANGE_N
    Result is not between 1 and 65535 for the row.

    The system aborts the request (ANSI mode) or transaction (Teradata mode). It does not update the base table or the sparse join index, and returns an error or failure, respectively.

    • Evaluates to a value, and
    • Expression is not CASE_N or RANGE_N
    Result is between 1 and 65535 for the row.

    The system stores the row in the appropriate partition, which might be different from the partition in which it was stored, and continues processing requests.

Expression evaluation errors, such as divide by zero, can occur during the evaluation of a partitioning expression. The system response to such an error varies depending on the session mode in effect.

Session Mode Expression Evaluation Errors Roll Back this Work Unit
ANSI Request that contains the aborted request.
Teradata Transaction that contains the aborted request.

Define your partitioning expressions to ensure that expression errors do not prevent the insertion of valid rows.