When working with virtual columns recently I came across the following error:

ORA-30553: The function is not deterministic
Cause: The function on which the index is defined is not deterministic
Action: If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.

So I though of writing about DETERMINISTIC Functions as I find it very important piece of information when working with function-based indexes, virtual columns or materialized views.

When creating functions (User Defined Functions), we can use DETERMINISTIC keyword or clause to indicate that the function will always return the same output or value for any given set of input argument values any point of time.

We must specify this keyword or clause if you intend to call the function in the function-based index expression, virtual column expression or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE.

Otherwise it will generate following error:

ORA-30553: The function is not deterministic
Cause: The function on which the index is defined is not deterministic
Action: If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.

When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than re-executing the function.

Notes from Oracle Doc:
“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. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function”

Important Note:
If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.