RESULT CACHE FUNCTION

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

 

• 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