1. Overview
This Document is about to store the function result into the server result cache.
2. Technologies and Tools Used
The following technologies have been used to achieve this in Oracle PL/SQL
3. Use Case
• It is a new feature in Oracle 11g.
• Cache the result of a Query or Query block for future reuse.
• Area in the shared pool can be used for storing and retrieving the cached results.
• Query results stored in the cache become invalid when data in the database objects being accessed by the query is modified.
4. Examples
Here I have some worked examples below,
create or replace function test_result_cache( p_in in number )
return number
result_cache
as
begin
sys.dbms_lock.sleep(10);
return( p_in );
end;
/ Function created.
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10) ———————
Elapsed: 00:00:10.35
SQL> select test_result_cache(10) from dual;
TEST_RESULT_CACHE(10) ———————
Elapsed: 00:00:00.00
