PL/SQL Function Result Cache

As you may already know result cache represents nice feature which is used to store results of query execution in the result cache area  (part of the SGA).

From 11g you can also cache pl/sql function results in the SGA and its available to all sessions which runs the application.
It’s easy to use, what you need to do is just mark the pl/sql function that its results should be cached.

You enable result caching for a function by adding the RESULT_CACHE clause in the function definition.
Also you can specify the optionally RELIES_ON clause which specifies tables/views on which the function results depends – since in this example the results depend on the HR.EMPLOYEES table I’ve added the optional keyword RELIES_ON

SQL> create function getEmpSalary(p_email varchar2)
  2  return number
  3  result_cache relies_on (employees)
  4  is
  5  v_salary employees.salary%type;
  6  begin
  7  select salary into v_salary from employees where lower(email) = lower(p_email);
  8  return v_salary;
  9  end;
 10  /

Function created.

Since this is test environment I flushed the cache and confirm that there are no cached information by querying the V$RESULT_CACHE_OBJECTS dictionary view. Continue reading