Oracle SQL查询未使用trunc(create_date)索引问题咨询

问题描述 #

我在Oracle数据库中执行以下查询，已在ORDERS表上创建trunc(create_date)函数索引，但执行时该查询未使用此索引：

SELECT ITEM, MAX("Orders") AS OP_ORD, MAX("SHIP") AS SHIP
FROM  ORDERS
WHERE  trunc(create_date) in(
        SELECT TRUNC(sysdate)-6-(select to_char(sysdate, 'd') from dual) FROM DUAL
         union
        SELECT TRUNC(sysdate)-5-(select to_char(sysdate, 'd') from dual) FROM DUAL
         union
        SELECT TRUNC(sysdate)-4-(select to_char(sysdate, 'd') from dual) FROM DUAL
         union
        SELECT TRUNC(sysdate)-3-(select to_char(sysdate, 'd') from dual) FROM DUAL
  )
GROUP BY ITEM

但如果从子查询中移除最后一条查询，如下所示，查询就会使用该索引：

SELECT ITEM, MAX("Orders") AS OP_ORD, MAX("SHIP") AS SHIP
FROM  ORDERS
WHERE  trunc(create_date) in(
       SELECT TRUNC(sysdate)-6-(select to_char(sysdate, 'd') from dual) FROM DUAL
         union
       SELECT TRUNC(sysdate)-5-(select to_char(sysdate, 'd') from dual) FROM DUAL
         union
       SELECT TRUNC(sysdate)-4-(select to_char(sysdate, 'd') from dual) FROM DUAL                  
)
GROUP BY ITEM

原因分析 #

这种差异是Oracle优化器基于数据量估算做出的执行计划选择：

• 当IN子查询返回3个日期值时，优化器判断通过函数索引定位符合条件的行，再进行分组聚合的成本更低——索引可以快速过滤出目标日期的数据，避免全表扫描。
• 当IN子查询增加到4个日期值时，优化器估算符合条件的数据量占表总数据的比例变高（一般超过10%-15%的阈值），此时认为全表扫描后再过滤、分组的成本比走索引更低。因为索引访问需要先查索引再回表取数据，当匹配行数过多时，回表的IO开销会超过全表扫描的开销。
• 另外，你子查询里的to_char(sysdate, 'd')返回当前星期几（结果依赖NLS设置），计算出的是连续4天的日期范围，优化器可能判定连续日期覆盖的数据范围更大，进一步拉高了对匹配行数的估算，最终放弃使用索引。
• 还有一种可能是表的统计信息不准确，导致优化器对匹配行数的估算出现偏差。可以尝试重新收集ORDERS表的统计信息修正这个问题：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'ORDERS', CASCADE => TRUE);

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