Rules for Using MERGE on Tables with Row-Partitioned Join Indexes - Teradata Database

Rules for Using MERGE on Tables with Row-Partitioned Join Indexes

Following are the rules for using MERGE on tables with row-partitioned join indexes:

For MERGE requests that insert or update a row in a base table that causes an insert into a join index with row partitioning, the partitioning expression for that index row must result in a value between 1 and the number of partitions defined for that level.

For MERGE requests that insert or update a row in a base table that causes an update of an index row in a join index with row partitioning, the partitioning expression for that index row after the update must result in a value between 1 and the number of partitions defined for that level.

Merging a row into a base table does not always cause inserts or updates to 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:

a Teradata Database checks the WHERE clause condition for its truth value after the update to the row.

IF the condition evaluates to …	THEN the system …
FALSE	deletes the row from the sparse join index.
TRUE	retains the row in the sparse join index and proceeds to stage b.

b Teradata Database evaluates the new result of the partitioning expression for the updated row.

IF the partitioning expression …	THEN Teradata Database …
evaluates to null and no appropriate NO RANGE and UNKNOWN option has been specified	aborts the request, does not update either the base table or the sparse join index, and returns an error message to the requestor.
evaluates to null and appropriate NO RANGE and UNKNOWN options have been specified	handles the row by storing it in either the NO RANGE or UNKNOWN partition for the sparse join index, and continues processing requests.
evaluates to a value	stores the row in the appropriate partition, which might be different from the partition in which it was previously stored, and continues processing requests.

Collation has the following implications for merging rows into a table defined with a character partitioning:

If a noncompressed join index with a character partitioning under either an MULTINATIONAL or CHARSET_COLL collation sequence is defined on a table and the definition for the collation has changed since the join index was created, Teradata Database aborts any request that attempts to merge a row into the table and returns an error to the requestor whether the merge would have resulted in rows being modified in the join index or not.

If the partitioning expression for a noncompressed join index involves Unicode character expressions or literals, and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, Teradata Database aborts any attempts to insert rows into the table and returns an error to the requestor.