Using Non-Deterministic UDFs as Conditions on an Index - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

A non-deterministic UDF is a UDF that does not always return identical results for identical inputs. If you omit the DETERMINISTIC clause in the CREATE FUNCTION or REPLACE FUNCTION statement, or if you specify the NOT DETERMINISTIC clause, the UDF is considered to be non-deterministic. Because non-deterministic UDFs are evaluated for each selected row, a condition on an index column that includes a non-deterministic UDF results in an all-AMP operation.

For example, consider the following table definition:

CREATE TABLE t1
   (c1 INTEGER
   ,c2 VARCHAR(9))
PRIMARY INDEX ( c1 );

Now consider the following function definition:

CREATE FUNCTION UDF_RAN(LowerBound INTEGER, UpperBound INTEGER)
RETURNS INTEGER
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
NOT DETERMINISTIC
EXTERNAL;

The following SELECT statement includes a condition on the c1 index column that invokes the non-deterministic UDF called UDF_RAN and results in an all-AMP operation:

SELECT *
FROM t1
WHERE c1 = UDF_RAN(1,12);