17.05 - UPDATE (Upsert Form) 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)
For a row inserted into 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:
  • the partitioning expressions for that join index row cannot evaluate to null, and
  • the partitioning expression must be an expression that is CASE_N or RANGE_N with a result between 1 and 65535 for the row.

Inserting 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 is:
  1. The system checks the WHERE clause condition for its truth value after the row insert.
  2. The system evaluates the condition, then does one of the following:
  3. If the condition evaluates to FALSE, the system deletes the row from the sparse join index.
  4. If the condition evaluates to FALSE, the system retains the row in the sparse join index.

You cannot assign either a value or a null to the system-derived columns PARTITION or PARTITION#L1 through PARTITION#L62 in an insert operation.

Collation mode has the following implications for upsert operations that insert rows into tables 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, the database aborts any request that attempts to insert or update a row in the table and returns an error to the requestor whether the insert or update would have resulted in rows being modified in the join index or not.

If the partitioning expression for a table or noncompressed join index involves Unicode character expressions or literals, and the system has been backed down to a release that has a different Unicode character set than the one in effect when the table or join index was defined, you cannot insert or update rows in the table with an upsert operation.

Insert or update a row in a base table that causes an insert into a join index with row partitioning such that a partitioning expression for that index row does not result in a value between 1 and the number of partitions defined for that level.

Insert or update a row in a base table that causes an update of an index row in a join index with row partitioning such that a partitioning expression for that index row after the update does not result in a value between 1 and the number of partitions defined for that level.

Updating a base table row 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:

  1. The system checks the WHERE clause condition for its truth value after the update to the row.
    Condition Result
    FALSE System deletes the row from the sparse join index.
    TRUE 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 Result
    • Evaluates to null, or
    • Expression that is not CASE_N or RANGE_N
    Not between 1 and 65535 for the row.

    The system aborts the request. It does not update the base table or the sparse join index, and returns an error.

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

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