15.00 - MERGE (Temporal Form) - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

MERGE (Temporal Form)

Purpose  

Performs a temporal merge of a source row set into a target table based on the temporal qualifier and whether any target rows satisfy a specified matching condition with the source row.

Syntax  

Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

Note: To ensure application portability to future ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.

 

Syntax Element …

Specifies …

CURRENT VALIDTIME

that the merge is current in the valid-time dimension.

Either the target table or the source table must support the valid-time dimension.

Caution:

CURRENT DML modifications can cause serializability issues for concurrent transactions. See Appendix C: “Potential Concurrency Issues with Current Temporal DML” for information on avoiding these issues.

VALIDTIME

SEQUENCED VALIDTIME

that the merge is sequenced in the valid-time dimension.

Either the target table or the source table must support the valid-time dimension.

period_expression

the period of applicability for the DML statement.

The period of applicability must be a period constant expression that does not reference any columns, but can reference parameterized values and the TEMPORAL_DATE or TEMPORAL_TIMESTAMP built-in functions.

The period of applicability can also be a self-contained noncorrelated scalar subquery that is always nonsequenced in the time dimensions regardless of the temporal qualifier for the DML statement.

Note: If a period_expression is specified, the valid-time column cannot be specified or referenced anywhere in the query. If the valid-time column is a derived period column, the component columns cannot be specified or referenced anywhere in the query.

If period_expression is omitted, the period of applicability defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP(n) WITH TIME ZONE) valid-time column, where precision n and WITH TIME ZONE are optional.

NONSEQUENCED VALIDTIME

that the merge is nonsequenced in the valid-time dimension.

Either the target table or the source table must support the valid-time dimension.

merge_statement

the syntax for the conventional form of the MERGE statement.

For details, see SQL Data Manipulation Language.

Required Privileges

The temporal form of MERGE requires the same privileges as the conventional form of MERGE.

Usage Notes  

For a table that supports transaction time, the temporal qualifier in the transaction-time dimension is CURRENT TRANSACTIONTIME.

All check, primary key, and temporal unique (current, sequenced, nonsequenced) constraints defined on the table are checked only on rows that are open in transaction time.

If either the target table or source table is a temporal table and the MERGE statement does not specify a temporal qualifier, the value of the session valid-time qualifier is used for the temporal table.

If the source row set is specified by a value list, the source is considered nontemporal.

A select subquery or a table specified as a source row set in the merge statement can reference a temporal table. The derived table can result in a temporal or nontemporal table, depending on the temporal qualifier used.

MERGE is not supported on tables that do not have primary indexes and column-partitioned tables.

The UPDATE portion of the MERGE statement follows the rules of the temporal UPDATE statement semantics and the INSERT portion of the MERGE statement follows the rules of the temporal INSERT statement semantics.

DML operations on tables defined with NORMALIZE produce a normalized set of modified rows. Some unmodified rows may be deleted from the target table as a result of the normalization.

When the target table is a normalized temporal table with transaction time, rows that are deleted as a result of the normalization are closed in transaction time.

Merging into a Valid-Time Table with a PPI

For all MERGE statements, an ON clause match condition must specify an equality constraint on the primary index of the target table. (Note that MERGE is not supported on tables without primary indexes and column-partitioned tables.) To qualify for the ANSI MERGE path, which provides enhanced optimization, if the target table has a PPI, the equality must also include the partitioning column to qualify a single partition. For information about the ANSI MERGE optimizer path, see SQL Data Manipulation Language.

Because the recommended partitioning expressions for temporal tables use only the END bound of the temporal column time periods, the match condition can similarly use the END condition in the equality constraint. END (valid_time_column) IS UNTIL_CHANGED and END (transaction_time_column) IS UNTIL_CLOSED can be used as equality constraints on temporal columns for temporal tables that use the recommended partitioning expressions (see “Partitioning Expressions for Temporal Tables” on page 78).

The ending bound of the valid-time of the target rows is seldom known in advance. A solution is to pre-join the source and target tables, using the same conditions in the USING clause, to determine the valid-time values in the target table.

The pre-join should use the NONSEQUENCED VALIDTIME qualifier (AND CURRENT TRANSACTIONTIME, if applicable). The pre-join must be a left outer join from the source in order to preserve the non-matching row set for insertion into the target table.

Example  

The following example uses a nontemporal source table for the merge. Note that the values to be inserted can be any values, even those that would go into a different partition.

CREATE MULTISET TABLE bi_tgt_tbl
     (
      pkey_field INTEGER,
      int2_field INTEGER,
      vtcol PERIOD(DATE) NOT NULL AS VALIDTIME,
      ttcol PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL 
            AS TRANSACTIONTIME)
PRIMARY INDEX ( pkey_field )
PARTITION BY CASE_N(
((END(vtcol)) >= DATE ) AND ((END(ttcol)) >= CURRENT_TIMESTAMP(6)),
((END(vtcol)) <  DATE ) AND ((END(ttcol)) >= CURRENT_TIMESTAMP(6)),
(END(ttcol )) < CURRENT_TIMESTAMP(6));

CREATE SET TABLE src_tbl
     (
      pkey_field INTEGER,
      int2_field INTEGER)
PRIMARY INDEX ( pkey_field );


SEQUENCED VALIDTIME
MERGE INTO bi_Tgt_tbl
USING   /* This block prejoins and determines the target valid time
           values */
(
  NONSEQUENCED VALIDTIME PERIOD (DATE'2009-12-15', DATE'2009-12-18')
  AND CURRENT TRANSACTIONTIME
  SELECT  s.pkey_field, s.int2_field, END(b_t.vtcol) vtend,           END(b_t.ttcol) ttend
  FROM  src_tbl s LEFT OUTER JOIN bi_tgt_tbl b_t
  ON s.pkey_field = b_t.pkey_field
) AS nonbi_srct ( pkey, int2, vtend, ttend)
ON (pkey_field = nonbi_srct.pkey) 
AND END(vtcol) = vtend 
AND END(ttcol) = ttend

WHEN MATCHED THEN
 UPDATE SET int2_field = nonbi_srct.int2
WHEN NOT MATCHED THEN
 INSERT ( nonbi_srct.pkey, nonbi_srct.int2, 
          PERIOD(TEMPORAL_DATE, UNTIL_CHANGED )
);

Matching Process

The following table describes the merge matching process when the target table is a temporal table.

 

Temporal Merge Type

Matching Process

Current

The matching condition is applied on all current rows in the target table. For a target table with transaction time, all conditions and modifications are applied only on open rows.

If the source table is temporal, only the current rows from the source table participate in the merge process. For a source table that has transaction time, only open rows participate in the merge process. To get the source row set, the system rewrites the source to be a derived table.

Sequenced

The matching condition is applied on all target rows that overlap the optional period of applicability. For a target table with transaction time, all conditions and modifications are applied only on open rows.

If the source table is temporal, only those source rows that overlap the optional period of applicability participate in the merge process. For a source table that has transaction time, only open rows participate in the merge process. To get the source row set, the system rewrites the source to be a derived table.

When both the source and target tables are temporal, sequenced join semantics are applied, meaning that the matching condition of the ON clause additionally includes the system-added overlap condition on the temporal columns of the source and target.

Nonsequenced

All of the existing restrictions that apply to the matching condition of the ON clause for the conventional form of MERGE apply in the valid-time dimension in a nonsequenced merge.

For a target table with transaction time, all conditions and modifications are applied only on open rows. For a source table that has transaction time, only open rows participate in the merge process. To get the source row set, the system rewrites the source to be a derived table.

Modification of Rows

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. In a PPI 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 PPI 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.

    Error Logging Tables

    You can create an error logging table that you associate with a temporal table when you want Teradata Database to log information about update and insert errors that occur during a MERGE operation on the temporal table.

    To create an error logging table, use the CREATE ERROR TABLE statement. Error logging tables are nontemporal tables. When you create an error logging table that you associate with a temporal table, the temporal columns in the temporal table are included in the error logging table as regular period data type columns.

    If a valid-time column in the target table is a derived period column, the beginning and end bounds of the inserted rows are assigned to the component columns of the derived period valid-time column in the target table.

    To enable error logging for a MERGE statement, specify the LOGGING ERRORS option.

    For the temporal form of MERGE, the Teradata Database error logging facilities consider the following errors as local errors:

  • Sequenced duplicate rows
  • Check constraint violations
  • Errors during row build such as division by zero
  • Nonlocal errors are complex updates and nonsequenced USI updates. Unique constraint violations on a temporal table are join index errors instead of nonlocal errors as on a nontemporal table.

    For details on how to create an error logging table, see “CREATE ERROR TABLE” in SQL Data Definition Language. For details on how to specify error handling for the INSERT … SELECT statement, see SQL Data Manipulation Language.

    Related Information

     

    For more information on...

    See...

    MERGE statement

    SQL Data Manipulation Language

    INSERT (temporal form)

    “INSERT/INSERT … SELECT (Temporal Forms)” on page 119

    row partitioning temporal tables

    “Partitioning Expressions for Temporal Tables” on page 78

    UPDATE (temporal form)

    “UPDATE (Temporal Form)” on page 164