Deep Dive — DETERMINISTIC

A promise, not a feature: "same input, same output". Oracle believes you — and builds indexes and skips calls on that belief.

Used in
Chapter 3 — Functions Inside SQL
Appendix entry
DETERMINISTIC
Related
PRAGMA UDF RESULT_CACHE WITH FUNCTION

DETERMINISTIC declares that a function's result depends only on its arguments — no table reads, no SYSDATE, no package state, no sequence. Oracle uses the declaration in three ways:

  1. Function-based indexes and virtual columns — only a deterministic function may be indexed; the index stores the function's output and must be able to trust it.
  2. Call elimination inside a statement — when the same function is called with the same arguments repeatedly within one fetch, Oracle may reuse the previous result instead of calling again.
  3. Materialized view query rewrite — a deterministic function in an MV can be matched against a query.

Use it on every pure function called from SQL. Never use it on a function that reads data — the results become wrong, silently.

SQL 
CREATE [OR REPLACE] FUNCTION <name> (<params>) RETURN <type>
  DETERMINISTIC
  [PARALLEL_ENABLE] [RESULT_CACHE] [PIPELINED]
IS | AS
  ...
END;

-- in a package: the keyword goes in BOTH the spec and the body
PACKAGE p IS FUNCTION f (x NUMBER) RETURN NUMBER DETERMINISTIC; END;
PACKAGE BODY p IS FUNCTION f (x NUMBER) RETURN NUMBER DETERMINISTIC IS ... END; END;

SQL 
CREATE OR REPLACE FUNCTION vat_amount (p_amount NUMBER) RETURN NUMBER
  DETERMINISTIC
IS
BEGIN
  RETURN ROUND(p_amount * 0.17, 2);
END;
/
SELECT order_id, amount, vat_amount(amount) FROM orders WHERE customer_id = :id;

Normalizing a phone number for lookups:

SQL 
CREATE OR REPLACE FUNCTION norm_phone (p_phone VARCHAR2) RETURN VARCHAR2
  DETERMINISTIC
IS
BEGIN
  RETURN REGEXP_REPLACE(p_phone, '[^0-9]', '');
END;
/
CREATE INDEX customers_phone_ix ON customers (norm_phone(phone));

SELECT customer_id FROM customers WHERE norm_phone(phone) = norm_phone(:input);   -- uses the index

Without DETERMINISTIC: ORA-30553: The function is not deterministic at CREATE INDEX.

SQL 
CREATE OR REPLACE PACKAGE call_counter AS
  n PLS_INTEGER := 0;
END;
/
CREATE OR REPLACE FUNCTION tier_rank (p_tier VARCHAR2) RETURN NUMBER
  DETERMINISTIC
IS
BEGIN
  call_counter.n := call_counter.n + 1;                       -- for the demo only
  RETURN CASE p_tier WHEN 'VIP' THEN 1 WHEN 'GOLD' THEN 2 ELSE 3 END;
END;
/
BEGIN
  call_counter.n := 0;
  FOR r IN (SELECT tier_rank(tier) AS rk FROM customers WHERE ROWNUM <= 100000) LOOP NULL; END LOOP;
  DBMS_OUTPUT.PUT_LINE('calls: ' || call_counter.n);          -- far fewer than 100,000
END;
/

With 3 distinct tier values and 100-row fetch buffers, the call count is roughly 3 per fetch — a few thousand instead of 100,000. Remove DETERMINISTIC and it is exactly 100,000. The elimination is per fetch buffer (Oracle keeps a small cache of recent argument/result pairs), so sorted or low-cardinality input benefits most.

SQL 
CREATE OR REPLACE FUNCTION customer_tier (p_id NUMBER) RETURN VARCHAR2
  DETERMINISTIC                                              -- WRONG: reads a table
IS
  v customers.tier%TYPE;
BEGIN
  SELECT tier INTO v FROM customers WHERE customer_id = p_id;
  RETURN v;
END;
/

Two failure modes: (1) inside one query, after a customer's tier changes mid-run, cached results return the old value; (2) a function-based index on it stores tiers as of index creation and never updates — WHERE customer_tier(customer_id) = 'VIP' returns stale rows forever. Oracle does not check. For a table-backed lookup, the right tool is RESULT_CACHE (dependency-tracked) — or a join.

SQL 
CREATE OR REPLACE FUNCTION vat_amount (p_amount NUMBER) RETURN NUMBER
  DETERMINISTIC
IS
  PRAGMA UDF;
BEGIN
  RETURN ROUND(p_amount * 0.17, 2);
END;
/

DETERMINISTIC reduces how many calls happen; PRAGMA UDF reduces what each call costs. See PRAGMA UDF.

  • The elimination cache is small and per fetch — on a 500M-row scan with high-cardinality arguments it eliminates almost nothing. The function still runs ~500M times. Only the inline SQL rewrite (CASE, join) removes the calls.
  • Function-based index on a huge table: every insert/update of the indexed columns calls the function. Keep it cheap and pure.
  • Changing the function body does not rebuild function-based indexes that use it. After a logic change: ALTER INDEX ... REBUILD, or the index and the function disagree.
  • DETERMINISTIC functions with VARCHAR2 arguments and NLS-dependent logic (UPPER, TO_CHAR with formats) are deterministic only if the session NLS settings are constant — an index built under one NLS_SORT may not match queries under another.

  1. Declaring a table-reading function DETERMINISTIC — stale results, no error.
  2. SYSDATE, SYSTIMESTAMP, DBMS_RANDOM, sequences, package variables inside — not deterministic by definition.
  3. Forgetting the keyword in the package body (it must match the spec) — PLS-00323.
  4. Expecting a guaranteed cache — it is an optimizer permission, not a promise. Measure, as in 3c.
  5. Function-based index not used: the query must call the function with the same expression text as the index definition (norm_phone(phone), not norm_phone(c.phone) in some versions — check the plan).
  6. ORA-30553 at CREATE INDEX — the function exists but lacks the keyword.