Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--Test #1

with data (id) as (
  select 1 from dual union all
  select 2 from dual
)
select /*+ result_cache */
    id
from
    data


ID


1

2


explain plan for
with data (id) as (
  select 1 from dual union all
  select 2 from dual
)
select /*+ result_cache */
    id
from
    data
select * from table(dbms_xplan.display)


PLAN_TABLE_OUTPUT


Plan hash value: 3389613416

 

-----------------------------------------------------------------------------------------------

| Id  | Operation        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                            |     2 |     6 |     4   (0)| 00:00:01 |

|   1 |  RESULT CACHE    | 478vfsvhadjt55zu0vzbphb9f5 |       |       |            |          |

|   2 |   VIEW           |                            |     2 |     6 |     4   (0)| 00:00:01 |

|   3 |    UNION-ALL     |                            |       |       |            |          |

|   4 |     FAST DUAL    |                            |     1 |       |     2   (0)| 00:00:01 |

|   5 |     FAST DUAL    |                            |     1 |       |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

 

Result Cache Information (identified by operation id):

------------------------------------------------------

 

   1 - column-count=1; name="with data (id) as (
  select 1 from dual union all
  select 2 from dual
)
select /*+ result_cache */
    id
from
    data"


--Test #2

--Note: db<>fiddle doesn't know how to handle the varray column.
with data (id, my_array) as (
  select 1, sys.odcivarchar2list('a', 'b', 'c') from dual union all
  select 2, sys.odcivarchar2list('d', 'e') from dual
)
select /*+ result_cache */
    id,
    my_array
from
    data
explain plan for
with data (id, my_array) as (
  select 1, sys.odcivarchar2list('a', 'b', 'c') from dual union all
  select 2, sys.odcivarchar2list('d', 'e') from dual
)
select /*+ result_cache */
    id,
    my_array
from
    data
select * from table(dbms_xplan.display)


PLAN_TABLE_OUTPUT


Plan hash value: 3389613416

 

-------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |      |     2 |    74 |     4   (0)| 00:00:01 |

|   1 |  VIEW            |      |     2 |    74 |     4   (0)| 00:00:01 |

|   2 |   UNION-ALL      |      |       |       |            |          |

|   3 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |

|   4 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------