Oracle日期时间列查询性能差异原因咨询

嘿，这个问题我之前帮团队排查过类似的情况，核心原因基本都和Oracle的执行计划选择、索引选择性以及统计信息准确性脱不了干系，咱们一步步拆解：

核心原因：执行计划的切换 #

你观察到的速度差异，大概率是Oracle对两个查询选择了完全不同的执行计划：

• 当用Last_Update_Date > TO_DATE('2018-01-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS')时，Oracle估算这个条件会过滤掉绝大多数数据，所以选择走Last_Update_Date字段上的索引（假设你给这个字段建了索引）。索引扫描只需要读取符合条件的索引条目和对应的表数据，IO量小，速度自然快。
• 换成2018-01-03 12:12:12时，Oracle的统计信息判断这个条件过滤后剩下的数据量很大（通常超过表总数据的10%-15%，不同Oracle版本阈值略有不同），它会认为全表扫描的成本更低，就切换到了全表扫描。全表扫描需要读取整张表的所有数据块，耗时自然大幅上升。

为什么Oracle会做出这样的估算？ #

1. 统计信息过时或不准确 #

如果你的表最近有大量的数据插入、更新，或者很久没收集过统计信息，Oracle就没办法准确判断过滤后的数据量。比如：

• 假设2018-01-03 00:00:00之后只有少量数据更新，但统计信息显示的是旧数据分布，Oracle就会觉得符合条件的数据少，走索引；
• 而2018-01-03 12:12:12之后的数据实际不多，但Oracle根据旧统计信息估算数据量很大，就选了全表扫描。

2. 直方图的影响 #

如果Last_Update_Date字段创建了直方图（尤其是频率直方图），Oracle会根据直方图里的数值分布来精准估算基数：

• 比如00:00:00可能是业务上的批量更新时间，很多数据的Last_Update_Date都集中在这个时间点，Oracle知道大于这个时间的数据很少；
• 而12:12:12是一个分散的时间点，直方图里这个值的出现频率低，Oracle会默认认为大于它的数据占比很高，所以选择全表扫描。

3. 索引选择性不足 #

如果Last_Update_Date字段的索引选择性很差（比如大部分数据的时间都集中在某个狭窄范围），Oracle会认为索引扫描的优势不大，在估算时更倾向于全表扫描。

怎么验证和解决？ #

1. 对比执行计划 #

分别对两个查询执行以下命令，查看执行计划的差异：

EXPLAIN PLAN FOR
SELECT * FROM 你的表名 
WHERE Last_Update_Date>TO_DATE(SUBSTR('2018-01-03 00:00:00',0,19),'YYYY-MM-DD HH24:MI:SS');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

再把时间换成12:12:12执行一遍，看是不是一个走INDEX RANGE SCAN（索引范围扫描），另一个走TABLE ACCESS FULL（全表扫描）。

2. 更新统计信息 #

执行以下命令更新表和索引的统计信息，让Oracle能准确估算数据量：

DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => '你的用户名',
    TABNAME => '你的表名',
    CASCADE => TRUE, -- 同时收集索引统计信息
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE -- 自动选择采样比例
);

3. 强制走索引（临时方案） #

如果确认索引扫描确实更高效，但Oracle还是选了全表扫描，可以用查询提示强制走索引：

SELECT * FROM 你的表名 /*+ INDEX(你的表名 索引名) */
WHERE Last_Update_Date>TO_DATE(SUBSTR('2018-01-03 12:12:12',0,19),'YYYY-MM-DD HH24:MI:SS');

不过这只是临时 workaround，最好还是通过更新统计信息让Oracle自动选择最优执行计划。

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