Modification of Rows - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

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.