Deletion and Row-Partitioned Join Indexes - Teradata Database

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.

IF the condition evaluates to …	THEN …
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.

IF the partitioning expression …	THEN …
evaluates to null, or is an expression that is not CASE_N or RANGE_N	the 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 is an expression that is not CASE_N or RANGE_N	the 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.

In this 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.