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