Oracle非聚集索引异常：单MIN/MAX查询快，组合查询极慢

问题现象 #

• 针对SP_ORD_1PC_PASS表的TOM列创建非聚集索引后：
  • 单独执行SELECT MAX(TOM) FROM SP_ORD_1PC_PASS或SELECT MIN(TOM) FROM SP_ORD_1PC_PASS，查询瞬间完成；
  • 同时查询MIN(TOM), MAX(TOM)，或执行SELECT * FROM SP_ORD_1PC_PASS ORDER BY TOM ASC这类排序操作时，耗时长达3分钟；
  • 添加看似无过滤效果的条件WHERE TOM > SYSDATE-1000000后，排序查询瞬间返回（该条件未过滤任何数据）。
• 额外说明：重建索引后问题依旧；其他带索引列的排序操作正常；已提供索引统计信息。

核心原因分析 #

这种差异本质是Oracle优化器执行计划选择逻辑导致的：

1. 单独查询MIN/MAX：优化器直接读取非聚集索引的叶子节点首尾数据（索引本身是有序的），无需扫描整个索引或表，所以速度极快。
2. 同时查询MIN+MAX或执行全表排序：优化器可能误判执行成本，选择了全表扫描+排序的路径，而非利用索引的有序性直接扫描索引。这种误判通常源于统计信息不准确（比如TOM列数据分布异常、统计信息未及时更新），导致优化器认为全表扫描的成本更低。
3. 添加WHERE TOM > ...条件后：即使条件未过滤数据，该条件触发优化器优先选择索引扫描路径——因为条件引用了索引列，优化器会尝试利用索引来满足查询，此时直接扫描有序的索引即可返回排序后的数据，无需额外排序，因此速度瞬间提升。

验证与解决方案 #

1. 更新索引统计信息：执行DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => '你的用户名', INDNAME => 'TOM列的索引名')，强制收集最新的索引统计数据，帮助优化器做出正确的执行计划选择。
2. 强制指定索引：在查询中添加优化器提示，强制走索引，比如：

   SELECT /*+ INDEX(SP_ORD_1PC_PASS TOM_INDEX_NAME) */ MIN(TOM), MAX(TOM) FROM SP_ORD_1PC_PASS;
   

   或

   SELECT /*+ INDEX(SP_ORD_1PC_PASS TOM_INDEX_NAME) */ * FROM SP_ORD_1PC_PASS ORDER BY TOM ASC;
   

3. 检查数据分布：确认TOM列是否存在大量空值、数据倾斜（比如某一时间段数据量异常大），这类情况也会干扰优化器的成本计算。

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