# 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](../course-plan.md) · Appendix entry: [DETERMINISTIC](../appendix.md#deterministic) · Related: [PRAGMA UDF](pragma-udf.md), [RESULT_CACHE](result_cache.md), [WITH FUNCTION](with-function.md)

---

## 1. What It Is and When

`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.

## 2. Syntax

```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;
```

## 3. Examples on the Course Schema

### 3a. A pure calculation

```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;
```

### 3b. Function-based index — the reason DETERMINISTIC exists

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`.

### 3c. Seeing the call elimination

```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.

### 3d. The lie — and what it costs

```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](result_cache.md) (dependency-tracked) — or a join.

### 3e. With PRAGMA UDF — the usual pair

```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](pragma-udf.md).

## 4. What Breaks at Scale

- 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.

## 5. Pitfalls

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.

## 6. Related

- [PRAGMA UDF](pragma-udf.md) — cheaper calls.
- [RESULT_CACHE](result_cache.md) — the correct cache for table-backed lookups.
- [WITH FUNCTION](with-function.md) — inline functions for one query.