Author: R. Singh

While working with virtual columns sometimes you come across the ORA-30553 error.

Error: ORA-30553

Message: The function is not deterministic.

Cause: The function on which the index is defined is not deterministic.

Action:

• If the function is deterministic then mark it DETERMINISTIC.
• If the function is not deterministic means 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.

In this article I will discuss how you can avoid this error by making your function deterministic and why do you need so.

DETERMINISTIC Functions:

DETERMINISTIC Functions are very important piece of information when working with function-based indexes, virtual columns or materialized views.

DETERMINISTIC keyword or clause:

When creating user defined functions we can use DETERMINISTIC keyword or clause.

DETERMINISTIC keyword or clause can be used 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.

Function-based index expression:

DETERMINISTIC keyword or clause must be specified if you intend to call the function in the function-based index expression.

Virtual Column Expression:

DETERMINISTIC keyword or clause must be specified while using virtual column expression.

Materialized View:

DETERMINISTIC keyword or clause must be specified you intend to call the function from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE.

Otherwise it will generate ORA-30553 error.

When not to use DETERMINISTIC clause?

DETERMINISTIC clause should not be used to define a function that uses package variables.

DETERMINISTIC clause should not be used to define a function that accesses the database in any way that might affect the return result of the function.

If Oracle Database chooses not to re-execute the function then the results of doing so will not be captured.

Changing the semantics of a function:

You must manually rebuild all dependent function-based indexes and materialized views if you subsequently change the semantics of the function, then.

Marking a function as Deterministic:

If you do not use DETERMINISTIC clause for any of the purposes mentioned above and if you know your function is deterministic you should mark it as such.

Execution skipping by CBO:

Suppose you write a function that you might use in any SQL that could lead to your function being execute thousands of even millions of times. In that case the deterministic clause will allow the CBO to skip the execution of your function.

The execution of your function will be skipped if it has done so already for a previous row in the result set. This leads to improved performance.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Amazing Tips to Fix Broken Oracle ASM Instance!!
   Inside Oracle Compression!!
   Great Tips on Tuning Database Materialized Views
   Oracle Server Security, Important Concerns!!