In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.

Little example with difference:

SQL> create or replace function f_deterministic(p varchar2) 2 return varchar2 3 deterministic 4 as 5 begin 6 dbms_output.put_line(p); 7 return p; 8 end; 9 / SQL> set arrays 2 feed on; SQL> set serverout on; SQL> select 2 f_deterministic(x) a 3 ,f_deterministic('literal') b 4 from (select 'not literal' x 5 from dual 6 connect by level<=10 7 ); A B ------------------------------ ------------------------------ not literal literal not literal literal not literal literal not literal literal not literal literal not literal literal not literal literal not literal literal not literal literal not literal literal 10 rows selected. not literal literal not literal not literal not literal not literal not literal

As you can see, ‘literal’ was printed once, but ‘not literal’ was printed 6 times, so it was returned from cache 4 times.

Also i want to show the differences in consistency between:

1. Calling a function with determinstic and result_cache;

2. Calling an operator for function with result_cache;

3. Calling an operator for function with deterministic and result_cache;

In this example I will do updates in autonomouse transactions to emulate updates in another session during query execution:



Tables and procedures with updates drop table t1 purge; drop table t2 purge; drop table t3 purge; create table t1 as select 1 id from dual; create table t2 as select 1 id from dual; create table t3 as select 1 id from dual; create or replace procedure p1_update as pragma autonomous_transaction; begin update t1 set id=id+1; commit; end; / create or replace procedure p2_update as pragma autonomous_transaction; begin update t2 set id=id+1; commit; end; / create or replace procedure p3_update as pragma autonomous_transaction; begin update t3 set id=id+1; commit; end; / [collapse]

Variant 1 create or replace function f1(x varchar2) return number result_cache deterministic as r number; begin select id into r from t1; p1_update; return r; end; / [collapse]

Variant 2 create or replace function f2(x varchar2) return number result_cache as r number; begin select id into r from t2; p2_update; return r; end; / create or replace operator o2 binding(varchar2) return number using f2 / [collapse]

Variant 3 create or replace function f3(x varchar2) return number result_cache deterministic as r number; begin select id into r from t3; p3_update; return r; end; / create or replace operator o3 binding(varchar2) return number using f3 / [collapse]

SQL> set arrays 2; SQL> select 2 f1(dummy) variant1 3 ,o2(dummy) variant2 4 ,o3(dummy) variant3 5 from dual 6 connect by level<=10; VARIANT1 VARIANT2 VARIANT3 ---------- ---------- ---------- 1 1 1 2 1 1 2 1 1 3 1 1 3 1 1 4 1 1 4 1 1 5 1 1 5 1 1 6 1 1 10 rows selected. SQL> / VARIANT1 VARIANT2 VARIANT3 ---------- ---------- ---------- 7 11 11 8 11 11 8 11 11 9 11 11 9 11 11 10 11 11 10 11 11 11 11 11 11 11 11 12 11 11 10 rows selected.

Test:

We can see that function F1 returns same results every 2 execution – it is equal to fetch size(“set arraysize 2”),

operator O2 and O3 return same results for all rows in first query execution, but in the second query executions we can see that they are incremented by 10 – it’s equal to number of rows.

What we can learn from that:

1. The use of the function F1 with result_cache and deterministic reduces function executions, but all function results are inconsistent with query;

2. Operator O2 returns consistent results, but function is always executed because we invalidating result_cache every execution;

3. Operator O3 works as well as operator O2, without considering that function is deterministic.

All tests scripts: tests.zip