如何检查'return as locator'的效果？能否通过列大小追踪变化？

参考《对象关系开发者指南/嵌套表定位器》：‘对于大型子数据集，可以返回父行和子数据集的定位器，以便按需访问子行；子数据集也可被过滤。使用嵌套表定位器可避免为每个父行不必要地传输子行。’

我使用return as locator后未发现列大小有差异，请查看以下示例代码，告知我如何检查return as locator的效果，是否可通过插入数据后的列大小变化或类似方式进行追踪？

示例代码 #

create type inner_table as table of number;
/

create type middle_table as table of inner_table;
/

create type outer_table as table of middle_table;
/

create table tab1
(
    col1    number,
    col2    outer_table
)
nested table col2
    store as col2_ntab (
        nested table column_value
            store as cval1_ntab (
                nested table column_value
                    store as cval2_ntab
                    return as locator));
/



insert into tab1
     values (2,
             outer_table (middle_table (inner_table (1,
                                                     2,
                                                     3,
                                                     4,
                                                     5),
                                        inner_table (10,
                                                     20,
                                                     30,
                                                     40,
                                                     50)),
                          middle_table (inner_table (1000,
                                                     2000,
                                                     3000,
                                                     4000,
                                                     5000),
                                        inner_table (10000,
                                                     20000,
                                                     30000,
                                                     40000,
                                                     50000))))
/

select /*+ NESTED_TABLE_GET_REFS +*/
       col1,
       col2,
       t1.*,
       t2.*,
       t3.*
  from tab1                     t0,
       table (t0.col2)          t1,
       table (t1.column_value)  t2,
       table (t2.column_value)  t3
/

验证方法 #

1. 查看执行计划（最直观验证） #

通过执行计划能直接看到是否使用定位器访问嵌套表：

EXPLAIN PLAN FOR
select /*+ NESTED_TABLE_GET_REFS +*/
       col1,
       col2,
       t1.*,
       t2.*,
       t3.*
  from tab1                     t0,
       table (t0.col2)          t1,
       table (t1.column_value)  t2,
       table (t2.column_value)  t3
/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

如果计划中出现TABLE ACCESS BY INDEX ROWID针对嵌套表存储表（如CVAL2_NTAB），同时伴随INDEX UNIQUE SCAN访问嵌套表主键索引，说明是通过定位器按需获取子行，而非一次性加载所有子数据。

2. 对比逻辑读差异 #

用SET AUTOTRACE ON STATISTICS查看查询的逻辑读：

• 先创建一个不带return as locator的对比表，插入相同数据
• 分别执行相同查询，对比两者的逻辑读数值
  使用定位器的版本逻辑读会显著降低（子数据集越大差异越明显），因为它只读取需要的子行，而非全部子数据。

3. 检查实际访问路径 #

通过V$SQL_PLAN查看执行时的操作：

SELECT operation, options, object_name
FROM v$sql_plan
WHERE sql_id = (SELECT sql_id FROM v$sql WHERE sql_text LIKE '%table (t2.column_value)%' AND rownum=1);

如果出现NESTED TABLE GET REFS相关操作，说明定位器已生效。

4. 关于列大小无变化的说明 #

父表中嵌套表列本身存储的是指向嵌套表存储表的指针（REF），无论是否使用return as locator，父表列的物理大小不会有明显差异。定位器的优化体现在查询时的数据传输和访问方式，而非父表列的存储大小，只有当子数据集极大时，一次性加载和按需加载的性能差异才会凸显。

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