Oracle: RESULT_CACHE Tables

Demonstration table

create table tq84_result_cache_force (
  id     number primary key,
  col_1  varchar2(10),
  col_2  varchar2(10)
)
result_cache(mode force);

create table tq84_result_cache_default (
  id     number primary key,
  col_1  varchar2(10),
  col_2  varchar2(10)
)
result_cache(mode default);

SQL Plan operations

explain plan for
select
   id
from
   tq84_result_cache_force
where
   col_1 = 'X';

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

Data dictionary

select
   table_name,
   result_cache
from
   user_tables
where
  table_name like 'TQ84_RESULT_CACHE_%';

Cleaning up

drop table tq84_result_cache_force;
drop table tq84_result_cache_default;

See also