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;

Oracle SQL Plan operation: RESULT CACHE

See also