含varray等复杂类型列的Oracle查询如何使用Query Result Cache

测试1：基础结果缓存触发验证 #

现有可正常触发Query Result Cache的查询，使用了提示/*+ result_cache */，SQL语句如下：

with data (id) as (
  select 1 from dual union all
  select 2 from dual
)
select /*+ result_cache */
    id
from
    data

执行计划显示RESULT CACHE已正常启用：

-----------------------------------------------------------------------------------------------
| 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="..."

测试2：含VARRAY列的缓存失效场景 #

保持查询逻辑不变，新增varray类型列，SQL语句如下：

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

执行计划显示未启用RESULT CACHE：

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

包含varray列的查询是否有方法启用Query Result Cache？

约束：不接受将varray元素提取为字符串的替代方案，要求查询中直接使用原生varray列，同时兼容SDO_GEOMETRY这类其他复杂数据类型。

可以正常启用，缓存失效的核心原因是Oracle Query Result Cache默认仅对SQL内置标量类型提供自动支持，VARRAY、嵌套表、SDO_GEOMETRY这类复杂对象/集合类型，需要显式标记支持结果缓存属性后，优化器才会允许对包含这类类型列的查询启用结果缓存。

具体操作方式如下：

• 不要直接修改SYS、MDSYS等系统Schema下的自带类型（比如测试用的SYS.ODCIVARCHAR2LIST、空间类型SDO_GEOMETRY），这类操作权限要求高，容易触发数据库内部功能异常、补丁冲突，生产环境禁止直接修改系统类型。
• 基于需要使用的目标类型，自定义带RESULT_CACHE属性的类型即可，示例：

  -- 自定义支持结果缓存的字符串VARRAY类型
  CREATE OR REPLACE TYPE my_cached_varchar_list AS VARRAY(32767) OF VARCHAR2(4000) RESULT_CACHE;
  /
  

  针对SDO_GEOMETRY这类复杂对象类型，在自定义类型定义的末尾加上RESULT_CACHE关键字，即可获得结果缓存支持。
• 将查询中使用的系统VARRAY/对象类型替换为你自定义的带缓存属性的类型后，再搭配/*+ result_cache */提示，执行计划就会正常出现RESULT CACHE算子，缓存功能正常生效。

额外注意事项：

• 带RESULT_CACHE属性的类型不能包含LOB类型字段，否则类型创建会直接报错
• 如果是存储函数返回复杂类型需要缓存，除了类型本身添加RESULT_CACHE属性，函数定义也需要加上RESULT_CACHE声明

内容的提问来源于stack exchange，提问作者User1974