Oracle 18c XML关联查询仅硬编码WHERE子句时性能优异的原因

性能骤降本质是谓词下推失效导致XML解析的计算量被放大了上百倍，和左连接的最终返回结果逻辑无关：

• 保留硬编码IN列表时，Oracle优化器可以在访问sde.gdb_items_vw的第一时间就应用过滤条件，仅读取列出的10条匹配i.name的元数据记录，后续的XML格式转换、xmltable解析编码值的操作仅需要处理这10条记录的内容，计算量极小，所以0.09秒就能返回结果。
• 删除IN列表后，优化器无法提前判断最终需要哪些域的记录，会先全量扫描sde.gdb_items_vw中所有i.name is not null的记录。这个视图是ArcSDE地理数据库的核心元数据视图，存储了整个库所有编码域、要素类、配置项的定义，通常包含数千甚至上万条记录，每一条都要执行XML类型转换、路径解析提取编码值的CPU密集型操作，等全量解析完所有记录后，才会执行左连接丢弃不匹配的行，大量计算完全浪费在最终用不到的记录上，耗时自然会暴涨到35秒。

左连接确实会在最终结果阶段过滤掉右表不匹配的行，但Oracle优化器不会默认把连接条件反向推到包含复杂XML解析、不可合并视图的子查询内部做提前裁剪，这种保守策略是为了避免改写查询导致结果错误，副作用就是产生大量无效计算。

不需要硬编码域名也能达到和原写法一致的性能，核心思路是提前用subtype视图里实际存在的域名做过滤，再执行XML解析，参考写法：

with
subtype as (select * from sub_lc_events_asset_class_activity_vw),
domain  as (select      
                cast(rownum as number(38,0)) as rownum_,
                x.code,
                x.description,
                i.name as domain_name
            from        
                -- 先过滤出实际会用到的域记录，再做XML解析
                (
                    select i.name, i.definition
                    from sde.gdb_items_vw i
                    where i.name is not null
                    and exists (
                        select 1 
                        from subtype s 
                        where s.subtype_field_domain = i.name
                    )
                ) i
            cross apply xmltable(
                '/GPCodedValueDomain2/CodedValues/CodedValue' 
                passing xmltype(i.definition)
                columns
                    code        varchar2(255) path './Code',
                    description varchar2(255) path './Name'
                ) x    
            )
select
    d.code as domain_code,
    d.description as domain_description,
    d.domain_name,
    s.subtype_code,
    s.subtype_description,
    s.subtype_field,
    s.subtype_field_domain,
    s.table_name as table_name
from
    subtype s
left join
    domain d
    on s.subtype_field_domain = d.domain_name

如果上面的写法优化器还是没有正确做谓词下推，可以在关联domain时加/*+ PUSH_PRED(d) */提示强制下推连接条件，稳定性不如直接改写子查询的方式。

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