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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | 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:
Test:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | 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. |
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