Oracle 19.9层级查询新增索引后性能下降优化咨询

性能下降原因分析 #

• 索引回表的随机IO开销过高：你新建的是普通二级索引，查询时需要先扫描索引拿到ROWID，再随机访问表数据块获取所有字段（因使用了SELECT *），如果符合ID+日期过滤条件的行数占总表比例超过5%~10%，随机IO的总开销会远高于全表扫描的顺序IO开销，执行计划预估成本低但实际运行耗时更高。
• 索引列顺序不合理：当前索引的前缀列是SENDD、SBEGD这类范围查询列，会导致索引范围扫描效率下降，范围条件后的ID列过滤无法走索引匹配只能走过滤，降低了索引过滤效率。
• 层级查询的递归访问放大了索引开销：CONNECT BY递归查询过程中，每一层都需要根据父ID查询子节点，索引回表的开销会被递归次数放大，进一步拖慢执行速度。
• 统计信息采样不足导致执行计划估算错误：执行计划备注显示使用了level 2的动态采样，采样数据量极少，导致优化器低估了索引扫描的实际开销，错误选择了成本更低但实际耗时更高的索引执行计划。

可行优化方案 #

方案1：创建覆盖索引避免回表 #

按「等值条件在前、范围条件在后、查询字段补齐」的规则创建覆盖索引，彻底消除回表开销，同时尽量不要使用SELECT *，只保留构建层级需要的字段：

-- 括号最后补全你实际需要的其他字段，不需要的字段不要加入
CREATE INDEX idx_zhr_struct_jmk4_opt ON zhr_struct_jmk4 (objid, id, sobid, sbegd, sendd) INCLUDE (其他需要查询的字段);

方案2：下推过滤条件减少扫描量 #

把日期过滤条件下推到递归逻辑中，不需要先全量过滤所有符合条件的行再做层级查询，大幅减少递归扫描的行数：

SELECT 你需要的字段
FROM zhr_struct_jmk4
WHERE id >= 9000000 AND ID <= 100000000
START WITH objid = '72002506'
  AND SBEGD <= :iv_begda
  AND sendd >= :iv_endda
CONNECT BY PRIOR id = sobid
  AND SBEGD <= :iv_begda
  AND sendd >= :iv_endda;

方案3：强制走全表扫描（适合过滤行数占比高的场景） #

如果符合ID+日期范围的行数占总表比例超过10%，可以加HINT强制走全表扫描，避免优化器选错执行计划：

SELECT /*+ FULL(a) */ *
FROM ( SELECT * FROM zhr_struct_jmk4
       WHERE id >= 9000000 AND ID <= 100000000
         AND SBEGD <= :iv_begda
         AND sendd >= :iv_endda ) a
START WITH objid = '72002506'
CONNECT BY prior a.id = a.sobid;

方案4：物化视图预计算（适合数据更新频率低的场景） #

如果组织层级数据不需要实时查询，可以按日期维度预计算层级关系生成物化视图，查询时直接访问物化视图可将耗时降到毫秒级。

内容的提问来源于stack exchange，提问作者Jakob Marius Kjær