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 | |
| ------------------------------------------------------------------------- |