Forum Stats

  • 3,875,139 Users
  • 2,266,810 Discussions
  • 7,912,093 Comments

Discussions

Query Result Cache: Support collection and object datatypes

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.

User_1871
1 votes

Active · Last Updated

Comments