Never trust a computer you can’t throw out of a window.
Steve Wozniak

Deterministic Function Caching

A function is considered deterministic if the outcome is the same if the input values are the same. Another thing is that the program should have no side effects. All the program changes are the return value and any out parameters. It is because of this consistent behavior, Oracle can build a cache based on the functions inputs and output. The same input results in the same output and there are no side-effects. So the second time the function is called with the same parameters there is no need for it to be executed again to get to the result.

1. CREATE OR REPLACE FUNCTION multiplication_table(left_in IN INTEGER
2.                                                ,right_in IN INTEGER) RETURN INTEGER
4. IS
6.   dbms_output.put_line(left_in || ' times ' || right_in);
7.   RETURN left_in * right_in;
8. END multiplication_table;
9. /
1. prompt using pure SQL
2. with numbers as (select rownum n
3.                      from dual
4.                     connect by level <= 10)
5. select mod(left_num.n,2) left_num
6.      , mod(right_num.n,5) right_num
7.      , multiplication_table(mod(left_num.n,2),mod(right_num.n,3))
8.   from numbers left_num
9.        cross join numbers right_num;

If you run the code above, then you will see that there are 100 rows selected (which is a expected) but there will be only 6 lines of output. The code in the function is called only once for every unique set of parameters. If the same parameters are used for the procedure call then the code is not executed again, but the ‘cached’ result is returned directy. It appears Oracle has a limited number of ‘slots’ available to store the results. The deterministic hint is only used when the function is called from pure SQL. If the function is called from PL/SQL or from within a SQL statement embedded in a PL/SQL program then the code will be executed every time and the deterministic hint is ignored completely.

Function Result Cache (Oracle Database 11g)

Prior to Oracle 11g, package based caching was the best option, when you wanted to cache data in a PL/SQL program. In fact, it was the only option available. The problem with this approach is that the cache cannot be shared across sessions. Every session connected has its own version of the cache in its own piece of memory. And, maybe even worse, the cache will not be updated automatically when then data this cache is based on changes. This means that a session may be using old data from the cache when the real data in the table is already updated.

Oracle Database 11g offers function result caching, which does pretty much the same thing as the deterministic hint described earlier, but the cache is shared between sessions and (maybe even more important) is automatically invalidated when the underlying data changes. Function result cache should be used on tables that are queried way more often then they are updated. If it is queried only a couple of times, before the table is updated (which results in invalidation of the cache) it might even slow down your execution.

When you use the Function Result Cache for a function, these are your benefits:

  • The database stores both input and return value in a separate cache for each function. That means, that is the cache gets invalidated for a specific function, this cache is flushed out without affecting the other caches. The cache is shared across all sessions connected and connecting to this instance. The cache is available till you flush it explicitly or the instance is restarted.
  • Caching occurs automatically whenever you call the function, either from SQL or PL/SQL. You don’t need to turn it on, except by defining the function to be Result Cached.
  • When changes are made to the objects the function is relying on, the cache is automatically invalidated.
  • When you call the function, Oracle first checks its cache to see if it already has the same values cached. If it does, then it just returns the cached result, without calling the function.
  • You don’t need to write any code to declare, populate and keep the cache up to date. All you have to do, is declaratively tell Oracle that the result of this function is to be cached.

The syntax for this clause is:

RESULT_CACHE [ RELIES_ON (table_or_view [, table_or_view2 ... table_or_viewN] ]

In Oracle 11g release 1 you needed to tell Oracle on what table your function relies. In 11gR2 this is no longer necessary because the database can now figure it out itself.

If there are uncommitted changes, the result cache is not used at all. The other session can still use the cached values, because to them, the data is still fresh. After a commit is issued the cache is invalidated

Here is a little example to show how Function Result Cache works:

1. create table t (empno number
2. , ename varchar2(10)
3. );

1. insert into t select e.empno, e.ename from scott.emp e
2. /

1. create or replace function getempname(empno_in in t.empno%type) return
2. varchar2 result_cache
3. is
4.   l_returnvalue t.ename%type;
5. begin
6.   dbms_output.put_line('Function getempname running');
7.   begin
8.     select e.ename
9.       into l_returnvalue
10.      from t e
11.     where 1=1
12.       and e.empno = empno_in;
13.   exception
14.    when no_data_found then l_returnvalue := 'Not Found';
15.    when too_many_rows then l_returnvalue := 'TMR';
16.   end;
17.   RETURN l_returnvalue;
18. end getempname;
19. /
20. sho err
21. grant all on getempname to public
22. /

1. select getempname(7499) from dual
2. /
3. select getempname(7499) from dual
4. /
5. insert into t (empno, ename) values (2912,'BAREL')
6. /
7. select getempname(7499) from dual
8. /
9. select getempname(7499) from dual
10. /

1. begin
2.   for indx in 1 .. 1000 loop
3.     dbms_output.put_line(getempname(7499));
4.   end loop;
5. end;
6. /
7. commit
8. /
9. begin
10.  for indx in 1 .. 1000 loop
11.    dbms_output.put_line(getempname(7499));
12.  end loop;
13. end;
14. /
15. drop function getempname
16. /
17. drop table t purge
18. /

You should not use the Function Result Cache when one or more of these criteria apply:

Your function...

  • defined in a module that has invoker’s rights or is in an anonymous block.
  • a pipelined table function
  • ...references dictionary tables, temporary table, sequences or any nondeterministic SQL function
  • ...has IN or IN OUT parameters.
  • ...has an IN parameter of either one of these type:
    • BLOB
    • CLOB
    • NCLOB
    • Collection
    • Object
    • Record
  • ...returns any of these types
    • BLOB
    • CLOB
    • NCLOB
    • Object
    • Record or PL/SQL collection that contains an unsupported return type


You have multiple ways of caching at your disposal. Assuming you are using 11G or higher your best bet is probably the Function Result Cache, because Oracle does all the heavy lifting for you. If you are using a lower version, you have to do it all yourself. If you are just using your code from a pure SQL environment then check out what making a function deterministic can do for your code. If you want or need to be in control of what is cached then using Package Based Caching can really help out a lot. As with most options in the Oracle database, there are multiple paths available. Choose either one and check it out on your instance, with your programs and your data. Take some time to explore the other option as well, if they apply. Then choose what to use.

Oracle PL/SQL Programming 5th Edition - Steven Feuerstein

Go to Caching - Part 1