17.05 - Merging into a Row-Partitioned Valid-Time Table - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

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 is row partitioned, the equality must also include the partitioning column to qualify a single partition. For information about the ANSI MERGE optimizer path, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

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).

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: Merging into a row-partitioned valid-time table

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 )
);