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

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

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 JAVA
NO SQL
PARAMETER STYLE JAVA
NOT DETERMINISTIC
EXTERNAL NAME 'JarUDF:UDFExample.udf_random';

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