Oracle中WHERE子句子查询触发LkPartProduct视图全表扫描的优化求助

• 核心问题本质：优化器错误估算结果集规模，颠倒执行顺序——本该先执行成本仅3的WO表索引扫描拿到33个匹配PartID，再用该小结果集关联LkPartProduct，反而选择全量扫描LkPartProduct后做合并连接，导致成本飙升20万倍以上。

1. 强制锁死执行顺序，消除优化器的随机选择偏差 #

• 放弃单一依赖不稳定的PRECOMPUTE_SUBQUERY提示，采用组合提示强制固定执行链路，完全避免优化器选错顺序：
  • 强制执行优先级：先跑WO表子查询，再关联LkPartProduct
  • 强制连接方式：对LkPartProduct走嵌套循环，而非触发全扫的合并连接
  • 强制索引命中：WO表的关联查询强制走已有的IM_WOID_ROUTINGID索引
    参考写法：

  SELECT /*+
    LEADING( sub_part lkpp )
    USE_NL( lkpp )
    INDEX(wo IM_WOID_ROUTINGID)
    NO_UNNEST( sub_part )
  */
  -- 原有查询业务逻辑
  FROM LkPartProduct lkpp
  WHERE lkpp.PartID IN (
    SELECT wo.PartID
    FROM WO wo
    WHERE wo.WOID = :传入WOID参数
  ) sub_part
  

• 注意：该写法和硬编码PartID的执行逻辑完全一致，会直接走LkPartProduct上PartID字段的索引，仅扫描匹配的33条记录，成本稳定在千位级。

2. 从根源修正统计信息偏差，减少优化器误判 #

• Oracle优化器的执行计划选择完全依赖统计信息，出现颠倒顺序的核心原因是统计信息失真：
  • 对WO表、LkPartProduct视图涉及的所有底层基表，重新收集最新的表、列级统计信息，重点覆盖WOID、PartID关联字段，避免使用数月之前的旧统计值。
  • 检查关联字段的数据类型一致性：确认WO表的PartID、LkPartProduct视图的PartID类型、长度完全匹配，消除隐式转换带来的估算误差——隐式转换会直接让索引失效，迫使优化器选择全表扫描。
  • 若关联字段存在数据倾斜（即某几个WOID对应返回的PartID极多、极少时），可直接通过DBMS_STATS.SET_COLUMN_STATS手工告知优化器对应列的实际基数分布，避免优化器盲目估算。

3. 用全局临时表固化小结果集，替代不稳定的提示逻辑 #

• 此前使用临时表优化效果差，本质是未给临时表同步统计信息，导致优化器仍把临时表当成和基表同规模的大结果集：
  • 建立会话级全局临时表，仅存储WO表查询出来的33个PartID值，插入完成后立刻对临时表收集一次轻量统计信息，明确告知优化器该结果集仅33行规模。
  • 主查询直接关联该临时表与LkPartProduct，搭配最小化的LEADING提示固定执行顺序，不依赖查询提示的特殊逻辑，计划稳定性远高于依赖PRECOMPUTE_SUBQUERY的写法。

4. 规避绑定变量窥视带来的计划固化问题 #

• 硬编码PartID时性能正常、传参时性能暴跌的场景，大概率是绑定变量窥视（Bind Peek）导致的计划缓存问题：第一次硬解析时遇到的绑定值对应返回结果量极大，优化器生成了全扫LkPartProduct的执行计划，后续哪怕传入的WOID仅对应33条记录，也会复用这个高成本计划。
  • 对该业务查询开启自适应游标共享，让优化器根据不同绑定值对应的实际返回行数，动态匹配最优执行计划。
  • 若不想调整全局数据库参数，可在查询中添加/*+ BIND_AWARE */提示，让优化器自动识别绑定值的敏感度，避免高成本计划被长期缓存。

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