Introduction

Disclaimer

This article on Oracle deterministic functions includes observations and statements related to performance that are inherently context-dependent and, in some cases, open to debate. Performance characteristics can vary significantly based on factors such as data volume, execution plans, database configuration, and specific use cases.

The intent of this article is not to present definitive conclusions, but to share perspectives and practical insights that may be useful for consideration. It is written with the goal of encouraging constructive discussion, critical thinking, and knowledge sharing within the community.

Readers are encouraged to validate the ideas presented here in their own environments and to contribute their experiences or differing viewpoints to further enrich the discussion.

What is a DETERMINISTIC function?

In Oracle, a DETERMINISTIC function is a function that always returns the same output for the same input values, regardless of when or how many times it is called.

Key idea

Same inputs:

• same output
• no side effects

Basic syntax

You declare it like this:

CREATE OR REPLACE FUNCTION get_upper(p_text VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
    RETURN UPPER(p_text);
END;

Why should we care and why is it important in Oracle?

Because Oracle CBO can optimise execution when it knows a function is deterministic.

It might unlock the following as Benefits:

• Avoiding repeated computation
• Enable better performance when using function-based indexes
• Improve query performance
• Enable query rewrite in some cases

Feature Usage

When should you consider using DETERMINISTIC?

• When your function:
  • Has no side effects (Calling the function does not change anything outside of its return value)
  • Does not depend on session state
  • Does not read rapidly changing data/tables
  • Produces consistent output for same inputs

Basic Example: deterministic function

CREATE OR REPLACE FUNCTION square_num(p_num NUMBER)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
    RETURN p_num * p_num;
END;

Usage:

SELECT square_num(5) FROM dual;

Always returns 25!

Important Use Case; Example 2: Function-Based Index

/This is how DETERMINISTIC feature shines :)

Problem: You often must search using a function

SELECT * 
FROM emp
WHERE UPPER(name) = 'JOHN';

This would would result in most cases on a Full Table Scan (FTS)which prevents index usage on column:

Which in turn, can result in suboptimal performance!

Possible Solution: Function-Based Index + DETERMINISTIC function

E.G. : create a function NORMALIZE_NAME very similar with one of the examples above:

CREATE OR REPLACE FUNCTION normalize_name(p_name VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
    RETURN UPPER(TRIM(p_name));
END;

Create index

CREATE INDEX idx_emp_name 
ON emp(normalize_name(name));

In many cases, with the index creation, the Oracle CBO might avoid doing a FTS – worth a try!

It is important to note that this approach does not automatically work in absolutely all cases, as I have encountered few situation where the FTS was still present even after the index creation. Still, worth spending a few moments to reflect on this option!

Example 3: Repeated Function Calls Optimisation

Without deterministic:

SELECT emp_id,
       expensive_function(salary)
FROM emp;

If multiple rows have the same salary, Oracle may still recompute.

With deterministic:

CREATE OR REPLACE FUNCTION expensive_function(p_salary NUMBER)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
    RETURN p_salary * 1.2;
END;

Oracle can now cache results internally (not guaranteed, but enabled).

Example 4: Data Warehousing / ETL Use Case

Transformation during an ETL flow, will update the price based on local tax rate:

CREATE OR REPLACE FUNCTION get_tax_rate(p_country VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
    IF p_country = 'FI' THEN RETURN 0.24;
    ELSIF p_country = 'DE' THEN RETURN 0.19;
    ELSE RETURN 0.20;
    END IF;
END;

During the ETL flow, price is recalculated:

SELECT price * get_tax_rate(country)
FROM sales_data;

The operation proves efficient when:
• Same country appears many times
• Oracle avoids recalculating repeatedly

Example 5: Materialized Views

I have notice significant performance improvements when DETERMINISTIC functions as they help Oracle trust that:
• Results won’t change unexpectedly
• Query rewrite is safe

Simplistic example:

CREATE MATERIALIZED VIEW mv_sales
AS
SELECT product_id,
       deterministic_func(price) AS adjusted_price
FROM sales;

So if you experience Materialised view related performance issues, it is worth having a look at using deterministic functions!

Critical Warning

Oracle does NOT enforce determinism automatically.

The developer can mislead Oracle, so this will compile:

    CREATE OR REPLACE FUNCTION bad_function
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN DBMS_RANDOM.VALUE;
    END;

This compiles — but it’s WRONG as if you run it several times in same conditions, it will get different results:

Consequences of erroneous usage can be

• Incorrect query results
• Corrupted indexes
• Hard-to-debug issues

So please remember o check the use of deterministic functions when troubleshooting!

Best Practices

I noticed better results when used with:
• Pure mathematical functions
• String normalization
• Static mappings
• Business rules without DB access

Real-World Use Cases Summary

Here are few othe ruse cases where I noticed good usage the feature:

• Search optimisation, Case-insensitive search
• ETL pipelines
• Repeated transformations
• Data warehousing
• Derived columns in materialized views
• Performance tuning; Avoid repeated expensive calculations

Non-Deterministic Examples

Be extremely cautious with following cases:

• SYSDATE
• function depends on table data which changes frequently
• sequence use
• use of session context: SYS_CONTEXT(‘USERENV’, ‘CURRENT_USER’)
• Time/session dependent

Performance Insight

Oracle does NOT guarantee either aching or single function evaluation!

Instead, DETERMINISTIC allows optimisations without forcing them!

For stronger caching → consider: RESULT_CACHE (better for many cases)

Conclusion

DETERMINISTIC is a declaration or promise the developer/DBA makes to Oracle — not something Oracle verifies automatically!

If that promise is true :
• You unlock performance
• Enable indexing tricks
• Improve scalability

If you break it: You risk silent data corruption o performance degradation!