17.10 - Modification of Rows - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

The following table describes the modification of rows for a temporal merge:

Temporal Merge Type Modification Details
Current A current merge results in a current update if matching rows are found; otherwise, it results in a current insert.

The SET clause of the UPDATE portion cannot reference the valid-time column or the transaction-time column as the name of a column to update.

The system may modify the temporal columns during an update of the matched row. For a row-partitioned table, if the modification of the temporal columns causes the rows to change partitions within the same AMP, the plan is sub-optimal since the row must be deleted and a new row inserted. The insert requires a spool.

The specification of a valid-time value is allowed in the INSERT portion in the merge when the INSERT uses a named list or assignment list. This inserted row can be into a different partition from the matched partition within the same AMP. This can be achieved if the row to be inserted is spooled in the same way as done when the matched row changes partition.

Sequenced If the source and target rows satisfy the matching condition for a sequenced merge, a sequenced update is performed.
  • When both tables are temporal, the valid-time portion updated is the intersection of period of applicability, the period of validity of the target table row, and the period of validity of the source row.

    The intersection of the period of validity of the source row and the period of applicability must be contained in the period of validity of the target row.

  • When only the target table is temporal, the valid-time portion updated is the intersection of the period of applicability and the period of validity of the target table row.
  • The SET clause of the UPDATE portion cannot reference the valid-time column or the transaction-time column as the name of a column to update.

If the source and target rows do not satisfy the matching condition, a sequenced insert is performed. The period of applicability, if specified, is ignored for the insert. The insert specification follows the rules for a simple sequenced insert and INSERT can specify a valid-time column value. The inserted row with the specified valid-time column value can be into a different partition from the matched partition within the same AMP.

Nonsequenced If matching rows are found for a nonsequenced merge, a nonsequenced update is performed; otherwise, a nonsequenced insert is performed.

The SET clause of the UPDATE portion can reference the valid-time column as the name of a column to update.

For a row-partitioned table where partitioning is on the valid-time column, the valid-time column can be modified.

The insert specification follows the rules of nonsequenced simple insert and the valid-time column value can be specified. The inserted row with the specified valid-time column value can be into a different partition from the matched partition within the same AMP.