MERGE ON Deterministic Functions in the Primary Condition - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

There are no restrictions on the specification of deterministic functions in the primary condition of a MERGE statement ON clause.

For example, the following MERGE request is valid:

     MERGE INTO t1
     USING t2
       ON a1=deterministic_udf(b2) AND b1=b2
     WHEN MATCHED THEN
       UPDATE SET c1=c2
     WHEN NOT MATCHED THEN
       INSERT (deterministic_udf(b2), a2, c2);

The deterministic UDF expression deterministic_udf(b2) executes only once for each row in the source table while spooling the source table t2. It is not executed again while the system processes the INSERT specification, but the value computed in the source spool is used in its place. This is a specific performance optimization and saves significant CPU time if the function is complicated.