The deterministic option marks a function that returns predictable results and has no side effects.
Function-based indexes, virtual column definitions that use PL/SQL functions, and materialized views that have query-rewrite enabled require special function properties. The
DETERMINISTIC clause asserts that a function has those properties.
DETERMINISTIC clause may appear at most once in a function declaration or definition. A function so marked is called deterministic.
A deterministic function must return the same value on two distinct invocations if the arguments provided to the two invocations are the same.
DETERMINISTIC function may not have side effects.
DETERMINISTIC function may not raise an unhandled exception.
If a function with a
DETERMINISTIC clause violates any of these semantic rules, the results of its invocation, its value, and the effect on its invoker are all undefined.
DETERMINISTIC clause is an assertion that the function obeys the semantic rules. If the function does not, neither the compiler, SQL execution, or PL/SQL execution may diagnose the problem and wrong results may be silently produced.
You must specify this keyword if you intend to invoke the function in the expression of a function-based index, in a virtual column definition, or from the query of a materialized view that is marked
REWRITE. When the database encounters a deterministic function, it tries to use previously calculated results when possible rather than reexecuting the function. If you change the function, then you must manually rebuild all dependent function-based indexes and materialized views.
Do not specify
DETERMINISTIC for a function whose result depends on the state of session variables or schema objects, because results might vary across invocations.
Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function.
DETERMINISTIC option appears, the compiler may use the mark to improve the performance of the execution of the function.
It is good programming practice to make functions that fall into these categories
Functions used in a
ORDER methods of a SQL type
Functions that help determine whether or where a row appears in a result set
In other chapters:
"CREATE FUNCTION Statement" for more information about standalone function syntax and semantics
In other books: