Purity level of functions

Purity level of functions:

CREATE OR REPLACE FUNCTION MY_FUNC RETURN INTEGER AS
BEGIN
MY_PACKAGE.MY_INTEGER := NVL(MY_PACK.MY_INTEGER,0) + 1;
RETURN(MY_PACK.MY_INTEGER);
END;

Then we use it in the following query:

SELECT a.*, my_func()
FROM my_table;

It is pretty easy to see that each time we run this query in a single session, we get a different result. This means the function is NON-DETERMINISTIC. The thing that makes it non-deterministic is that it UPDATES the PACKAGE STATE.

This is usually fine though, because SYSDATE is also non-deterministic, and we can use that in most places.

However, there are restrictions with non-deterministic functions. For example, you cannot use them in a Materialised View.

The Purity Level of a function tells us what it does and does not do, such as read/write database state, or read/write package state. Generally, a function that writes neither database nor package state is usually deterministic (unless it uses SYSDATE or some other non-deterministic feature).

Functions can be used in Materialised Views, Index Types, Function-based indexes, user-defined aggregation functions, and probably lots of other stuff.

Depending on the purity-level of the function, it may be banned from use in one or more of these areas, or it may be permitted in a limited fashion (eg. may not parallelise).