Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Query Result Cache: Support collection and object datatypes

User_1871Jul 5 2022

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.

Comments
Post Details
Added on Jul 5 2022
2 comments
176 views