分区拆分后查询性能退化：全表扫描替代索引扫描的问题求助

嗨，针对你遇到的分区拆分后查询性能骤降、执行计划从索引扫描变为全表扫描的问题，我帮你梳理几个常见的排查方向和解决办法，毕竟这种UAT与Prod执行计划不一致的情况，大多和统计信息、索引状态或优化器的成本判断逻辑有关：

一、先排查索引的有效性状态 #

分区拆分操作（尤其是对原有大分区拆分）很可能导致关联索引出现**失效（UNUSABLE）**或碎片化问题：

• 你可以先查询索引状态，以Oracle为例执行：

  SELECT index_name, status, partitioned FROM user_indexes WHERE table_name = 'A';
  

  如果发现索引状态是UNUSABLE，或者分区索引的部分分区状态异常，需要重建索引：
  • 全局索引重建：ALTER INDEX idx1 REBUILD;
  • 分区索引的单个分区重建：ALTER INDEX idx1 REBUILD PARTITION [拆分后的小分区名];

二、更新分区及表的统计信息 #

数据库优化器（CBO）依赖准确的统计信息来计算执行计划成本。拆分分区后，原有统计信息无法反映新的分区数据分布，优化器可能错误判断为全表扫描成本更低：

• 收集全表及分区级的最新统计信息，同样以Oracle为例：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(
      ownname => '你的用户名',
      tabname => 'A',
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      cascade => TRUE, -- 同时收集索引统计
      granularity => 'ALL' -- 包含分区、子分区及表级统计
  );
  

  注意：如果拆分的分区数据量很大，可能需要调整estimate_percent参数或手动指定收集该分区的统计。

三、对比Prod与UAT的执行计划，定位差异原因 #

你可以分别在Prod和UAT环境中执行相同的慢查询，查看执行计划细节：

• 生成执行计划的命令（Oracle）：

  EXPLAIN PLAN FOR [你的慢查询语句];
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  

  重点关注以下几点：
  • **分区修剪（Partition Pruning）**是否生效：UAT环境中是不是没有正确过滤掉不需要的分区，导致扫描范围扩大？
  • 优化器计算的索引扫描成本 vs 全表扫描成本：是不是因为统计信息过期，导致优化器认为全表扫描的I/O成本更低？
  • 有没有出现索引的**选择性（Selectivity）**判断错误：比如拆分后列的分布变化，优化器误以为索引的过滤效果差？

四、检查优化器参数一致性 #

确认UAT与Prod环境的优化器相关参数是否一致，比如：

• OPTIMIZER_MODE：是不是Prod用的是ALL_ROWS而UAT是FIRST_ROWS_xx？
• OPTIMIZER_INDEX_COST_ADJ：该参数会影响优化器对索引扫描成本的计算，若UAT的参数值设置过高，可能导致优化器放弃索引扫描。

五、其他可能的点 #

如果是局部索引（Partitioned Index），拆分分区时可能需要同步维护索引分区；另外，若你的查询涉及分区键column4的范围查询，拆分后的小分区可能让优化器的分区修剪逻辑需要重新适配，这时候更新统计信息就尤为关键。

备注：内容来源于stack exchange，提问作者Astitva