Function calls of a UDF with constant input values are referred to as uncorrelated function calls. Constant input values do not refer to the SQL statement which contains the function call,
Uncorrelated function calls are handled by SESAM/SQL as follows when the statement is executed:
Function values of uncorrelated function calls are calculated once only to evaluate conditions.
However, they are recalculated every time for the following output values:
in SELECT lists
for ORDER BY values
for values in INSERT rows
for UPDATE... SET ... values
for the INSERT- / UPDATE values in a MERGE statement
Example
SELECT f(1,2) FROM t WHERE col < g(5+4,8,9)
The function calls f(1,2) and g(5+4,8,9) of this SQL statement are uncorrelated.
The function g is calculated once only in order to evaluate the records of t. The set of hits of the query is then determined with this constant result. This also enable indexes to be used in the condition evaluation.
In the SELECT list, on the other hand, the f function is recalculated for each set of hits.
VOLATILE / IMMUTABLE annotations
The /*% VOLATILE %*/ and /*% IMMUTABLE %*/ annotations control the execution of uncorrelated function calls. In a function call, they are accepted only between the name of the function and the opening parenthesis for the function parameters. In any other position these annotations lead to a syntax error for the statement.
When /*% VOLATILE %*/ is specified, the function value is always recalculated.
When /*% IMMUTABLE %*/ is specified in an uncorrelated function call, the function value is not calculated again. The function value calculated beforehand is used. The function value is recalculated when the first function call takes place.
When these annotations are not specified, the SESAM/SQL procedure described above is used.
Example
SELECT f /*% VOLATILE %*/ (1,2)
FROM t WHERE col < g /*% IMMUTABLE %*/ (5+4,8,9)
These function calls map the existing SESAM/SQL procedure with annotations.
SELECT f /*% IMMUTABLE %*/ (1,2)
FROM t WHERE col < g /*% VOLATILE %*/ (5+4,8,9)
Specifying the annotations always causes function g to be recalculated. Function f is only calculated once.