Deterministic Functions

What are Deterministic Functions? How they help in improving Query Performance?

A function is called deterministic if it returns the same result value whenever it is called with the same values for its arguments.

When you define a function, you can simply add the DETERMINISTIC option to the declaration section, making sure that the function (or any functions or procedures it calls) does not depend on the state of session variables or schema objects as the results may vary across invocations.

This option instructs the optimizer that it may use a cached result whenever it encounters a previously calculated result.

Function-based indexes can only use functions marked DETERMINISTIC. The same goes for materialized views with REFRESH FAST or ENABLE QUERY REWRITE. One restriction is that you cannot define a nested function as deterministic.

EXAMPLE: SUBSTR is a Deterministic Function because each time you call SUBSTR with the same parameter values the same result will be returned

NOTE: A function should never be created as deterministic unless it will ALWAYS return the same value given the same parameters.

Let’s take an Example of Deterministic Function with the following code:

CREATE OR REPLACE FUNCTION RETURN_STRING (INPUT_STR IN VARCHAR2, P_START IN INTEGER, P_END IN INTEGER) RETURN VARCHAR2 IS

BEGIN

RETURN (SUBSTR (INPUT_STR, P_START, P_END- P_START+1));

END;

As long as you pass in, for example, “ABCDEF” for the string,3 for the start, and 5 for the end, RETURN_STRING will always return “CDE”.

Now, if that is the case, why not have Oracle save the results associated with a set of arguments? Then when the next function is called with those arguments, it can return the result without executing the function!

You can achieve this effect by adding the DETERMINISTIC clause to the function’s header, as in the following:

CREATE OR REPLACE FUNCTION RETURN_STRING (INPUT_STR IN VARCHAR2, P_START IN INTEGER, P_END IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS

BEGIN

RETURN (SUBSTR (INPUT_STR, P_START, P_END- P_START+1));

END;

By taking this step, I can now use this function in a function-based index. Even better, Oracle will, under very specific circumstances, cache the IN-Argument values and the RETURN values, and then avoid executing the function body if the same inputs are provided.

You must declare a function DETERMINISTIC in order for it to be called in the expression of a function-based index, or from the query of a Materialized View if that view is marked REFRESH FAST or ENABLE QUERY REWRITE.

Oracle has no way of reliably checking to make sure that the function you declare to be Deterministic actually is free of any side effects. It is up to you to use this feature responsibly. Your deterministic function should not rely on package variables, nor should it access the database in a way that might affect the result set.