Oracle 19c新建索引统计信息异常及执行计划问题咨询

一、预期行数（E-Rows）与实际行数（A-Rows）偏差的统计信息来源 #

Oracle优化器的预期行数（E-Rows）基于表和列的统计信息计算，核心来源包括：

• 表的总行数（NUM_ROWS）、列的唯一值数量、直方图数据等基础统计项，用于计算单个条件的过滤选择性。
• 多条件查询场景下，优化器默认假设条件之间相互独立，会分别计算每个条件的过滤行数后相乘得到最终预期值。如果STARTDATE和ENDDATE存在强相关性（比如绝大多数行的时间范围不包含目标日期），但优化器未收集到这种关联统计（如未创建多列直方图），就会导致估计值严重偏离实际。
• 若表的统计信息陈旧（长期未收集或数据发生大量变更），优化器基于过时的NUM_ROWS、列分布数据计算，也会出现明显偏差。

你的场景中，实际仅50行符合条件但预期达33M，大概率是优化器未捕捉到STARTDATE与ENDDATE的时间范围相关性，或是表的统计信息已过期，导致错误估计单个条件的过滤比例后，叠加独立假设的计算逻辑得出离谱结果。

二、新建索引后统计信息仍不准确、缺失A-Rows的原因 #

1. 缺失A-Rows的原因 #

你拆分了两个HINT块：

select  /*+ INDEX(mytable,MY_TABLE_TEST) */
      /*+ gather_plan_statistics */

Oracle要求HINT必须写在同一个/*+ ... */块内，拆分写法会导致gather_plan_statistics失效，因此执行计划无法收集实际行数（A-Rows）数据。正确写法应为：

select /*+ INDEX(mytable,MY_TABLE_TEST) gather_plan_statistics */
  id  
from MY_TABLE mytable
WHERE mytable.STARTDATE <= to_date('20032024','DDMMYYYY')
  AND mytable.ENDDATE >= to_date('20032024','DDMMYYYY');

2. 索引范围扫描预期行数仍不准确的原因 #

• 索引仅单列，无法覆盖关联条件：新建的MY_TABLE_TEST索引仅包含ENDDATE列，优化器计算ENDDATE >= '2024-03-20'的过滤行数后，仍需依赖表中STARTDATE的旧统计信息，且依然默认两个条件独立，导致估计值偏大。
• 表的统计信息未更新：即使创建了新索引，表的整体统计（尤其是STARTDATE与ENDDATE的相关性）仍为旧数据。未重新收集表统计时，优化器无法获取两列的关联关系，依然沿用错误的计算逻辑。
• 无合适的直方图：若STARTDATE或ENDDATE数据分布不均（如大量数据集中在某一时间段），但未创建直方图，优化器会按均匀分布假设计算选择性，引发估计偏差。

解决建议 #

• 重新收集表的全量统计，同时收集列相关性信息：

  exec dbms_stats.gather_table_stats(ownname => '你的用户名', tabname => 'MY_TABLE', method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => true);
  

• 针对强关联的STARTDATE和ENDDATE创建多列直方图：

  exec dbms_stats.gather_table_stats(ownname => '你的用户名', tabname => 'MY_TABLE', method_opt => 'FOR COLUMNS (STARTDATE, ENDDATE) SIZE AUTO');
  

• 确保HINT写法正确，才能正常获取实际执行的A-Rows数据。

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