Deferred Deletion and Join Indexes - Teradata Vantage

Deletion and Row-Partitioned Join Indexes

The system ends the request (ANSI mode) or transaction (Teradata mode) and 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 so that the row 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 ends the request (ANSI mode) or transaction (Teradata mode) and 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 may be different from its current partition, 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.

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