Search notes:

Oracle SQL Plan operation: RESULT CACHE

Create a demonstration table

create table tq84_tab (
   id   number,
   num  number,
   txt  varchar2(50)
);

Use the result_cache hint to select rows where id = 1:

explain plan for
select /*+ result_cache */
   num,
   txt
from
   tq84_tab where id = 1;

select * from table(dbms_xplan.display(format=>'basic'));
-- ---------------------------------------------------------
-- | Id  | Operation          | Name                       |
-- ---------------------------------------------------------
-- |   0 | SELECT STATEMENT   |                            |
-- |   1 |  RESULT CACHE      | 6w77fwvfsyy46770kdfmyz6mj9 |
-- |   2 |   TABLE ACCESS FULL| TQ84_TAB                   |
-- ---------------------------------------------------------

Same query again. Note that name is the same as in the prior query (6w77fwvfsyy46770kdfmyz6m9):

explain plan for
select /*+ result_cache */
   num,
   txt
from
   tq84_tab where id = 1;

select * from table(dbms_xplan.display(format=>'basic'));
--
-- ---------------------------------------------------------
-- | Id  | Operation          | Name                       |
-- ---------------------------------------------------------
-- |   0 | SELECT STATEMENT   |                            |
-- |   1 |  RESULT CACHE      | 6w77fwvfsyy46770kdfmyz6mj9 |
-- |   2 |   TABLE ACCESS FULL| TQ84_TAB                   |
-- ---------------------------------------------------------

Select where id = 99. Note how the name changes to 89du3u6zdwmd775nvp5z7x7gtk:

explain plan for
select /*+ result_cache */
   num,
   txt
from
   tq84_tab where id = 99;

select * from table(dbms_xplan.display(format=>'basic'));
-- ---------------------------------------------------------
-- | Id  | Operation          | Name                       |
-- ---------------------------------------------------------
-- |   0 | SELECT STATEMENT   |                            |
-- |   1 |  RESULT CACHE      | 89du3u6zdwmd775nvp5z7x7gtk |
-- |   2 |   TABLE ACCESS FULL| TQ84_TAB                   |
-- ---------------------------------------------------------

Cleaning up

drop table tq84_tab;

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1758199277, '216.73.216.150', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/statement/execution/plan/operations/result-cache/index(103): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

Oracle SQL Plan operation: RESULT CACHE

See also