Query Result Cache:
Regarding the Query Result Cache hint /*+ result_cache */ :
15 Tuning the Result Cache
A result cache is an area of memory, either in the Shared Global Area (SGA) or client application memory, that stores the results of a database query or query block for reuse. The cached rows are shared across SQL statements and sessions unless they become stale.
As far as I can tell, queries with collection and object datatypes aren't supported.
-------------------------------------------------------------
Test #1:
A query with a simple number column successfully invokes the Query Result Cache hint: /*+ result_cache */.
with data (id) as (
select 1 from dual union all
select 2 from dual
)
select /*+ result_cache */
id
from
data
Line 2 in the explain plan shows the RESULT CACHE being used:
-----------------------------------------------------------------------------------------------
| 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="..."
Test #2:
The next query is the same, except I've added a varray column (a collection):
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
The explain plan shows that the RESULT CACHE isn't being used.
-------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------
db<>fiddle
I have the same problem for object datatypes like SDO_GEOMETRY.
-------------------------------------------------------------
Idea:
Could the Query Result Cache be enhanced to support queries with collection and object datatypes?
Feel free to let me know if I've misunderstood something.