Writing Deterministic Functions and How the Optimizer Rewards You

What Does Deterministic Mean?

A function is deterministic if it always returns the same output for the same set of input arguments, with no side effects. Mathematical functions are the classic example: SQRT(25) always returns 5, no matter when or how many times you call it. Many utility functions in PL/SQL share this property. A function that formats a date string, calculates a checksum, or derives a status label from a numeric code is typically deterministic.

Oracle allows you to declare this property explicitly using the DETERMINISTIC keyword:

CREATE OR REPLACE FUNCTION calculate_tax(
   p_amount   IN NUMBER,
   p_tax_rate IN NUMBER
)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
   RETURN ROUND(p_amount * p_tax_rate / 100, 2);
END calculate_tax;
/

How the Optimizer Uses This Information

When you mark a function as DETERMINISTIC, you are giving the Oracle optimizer permission to cache and reuse results. If a query calls your function with the same arguments multiple times (which is common in queries with GROUP BY, joins, or repeated column expressions), the optimizer may execute the function only once per unique set of inputs rather than once per row.

This can produce dramatic performance improvements. Consider a query that joins two tables on a computed key:

SELECT a.order_id, b.product_name
  FROM orders a
  JOIN products b
    ON normalize_code(a.product_code) = normalize_code(b.product_code);

If normalize_code is deterministic and the table has 100,000 rows but only 500 distinct product codes, the optimizer can reduce 200,000 function calls to approximately 1,000. The impact on query elapsed time can be enormous.

Deterministic Functions and Function Based Indexes

One of the most powerful applications of deterministic functions is in function based indexes. Oracle requires that any function used in a function based index must be declared DETERMINISTIC, because the index entries must be stable over time:

CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- Or with your own function:
CREATE INDEX idx_orders_norm_code ON orders(normalize_code(product_code));

Without the DETERMINISTIC keyword, Oracle will refuse to create the index. This is one area where the keyword is not just a performance hint but a hard requirement.

The PRAGMA UDF Companion

Starting in Oracle 12c, you can pair the DETERMINISTIC keyword with PRAGMA UDF (User Defined Function) to further reduce the overhead of calling PL/SQL functions from SQL. PRAGMA UDF tells the compiler to optimize the function for being called from SQL contexts rather than PL/SQL contexts, reducing the context switch penalty:

CREATE OR REPLACE FUNCTION format_phone(p_raw IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
IS
   PRAGMA UDF;
BEGIN
   RETURN '(' || SUBSTR(p_raw,1,3) || ') '
       || SUBSTR(p_raw,4,3) || ' '
       || SUBSTR(p_raw,7);
END format_phone;
/

When NOT to Use DETERMINISTIC

Never mark a function as DETERMINISTIC if it reads from database tables (the data could change), calls SYSDATE or SYSTIMESTAMP, generates random numbers, depends on session state or NLS settings, or has any side effects like writing to a log table. Lying to the optimizer by marking a nondeterministic function as DETERMINISTIC will produce incorrect query results because Oracle will serve cached results when it should be calling the function fresh.

Version Differences

In Oracle 19c, the optimizer’s ability to cache deterministic function results within a single SQL execution was well established. Oracle 21c improved the scalar subquery caching mechanism that interacts with deterministic functions, and in 23ai the optimizer has become more aggressive about recognizing opportunities to eliminate redundant function calls. The RESULT_CACHE feature (available since 11g) can be combined with DETERMINISTIC for functions that are expensive to compute but called frequently with the same arguments across different sessions, though the two features serve different purposes and should be applied thoughtfully.