RESULT_CACHE Hint in 12.1.0.2

Every new Oracle Database patch introduces not only documented features, but also undocumented ones. In this short post I would like to provide information about an enhancement of the RESULT_CACHE hint.

According the documentation, the RESULT_CACHE hint has no parameter. But, as of 12.1.0.2, at least the SNAPSHOT parameter is supported. Its purpose is to specify how much time (in seconds) after the creation the result cache entry has to expire. In other words, you can specify that a specific entry expires even though the objects is based on doesn’t change.

The following example illustrates:

SQL> set time on

18:14:47 SQL> execute dbms_result_cache.flush

18:14:47 SQL> SELECT /*+ result_cache(snapshot=10) */ count(*) FROM scott.emp;

  COUNT(*)
----------
        14

18:14:47 SQL> SELECT status
18:14:47   2  FROM v$result_cache_objects
18:14:47   3  WHERE type = 'Result'
18:14:47   4  AND creator_uid = sys_context('userenv','current_userid');

STATUS
---------
Published

18:14:47 SQL> execute dbms_lock.sleep(10)

18:14:57 SQL> SELECT status
18:14:57   2  FROM v$result_cache_objects
18:14:57   3  WHERE type = 'Result'
18:14:57   4  AND creator_uid = sys_context('userenv','current_userid');

STATUS
---------
Expired

18:14:57 SQL> SELECT status
18:14:57   2  FROM v$result_cache_objects
18:14:57   3  WHERE type = 'Result'
18:14:57   4  AND creator_uid = sys_context('userenv','current_userid');

STATUS
---------
Invalid