Use Query Result Cache in query with varray

Question

Test #1:

I have a query that 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:

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

---

Question:

Is there a way to use the Query Result Cache in query that has a varray column?

I'd don't want to use a workaround where I extract the varray elements as a string. I'm hoping to use a proper varray column in my query, as well as other complex datatypes like SDO_GEOMETRY.

Answer 1

It looks like collection columns aren't supported by the RESULT_CACHE.

Oracle 18c - Restriction on RESULT_CACHE

RESULT_CACHE is disallowed on functions with OUT or IN OUT parameters.

RESULT_CACHE is disallowed on functions with IN or RETURN parameter of (or containing) these types:

• BLOB

• CLOB

• NCLOB

• REF CURSOR

• Collection

• Object

• Record or PL/SQL collection that contains an unsupported return type

RESULT_CACHE is disallowed on function in an anonymous block.

RESULT_CACHE is disallowed on pipelined table function and nested function.