Oracle查询添加fetch next 10 rows only仍超1小时？求原因及提速方案

为什么fetch next 10 rows only没起到预期效果？ #

你的查询条件eff_date like '%JAN-18'使用了前缀通配符的模糊匹配，这种写法导致Oracle无法利用eff_date字段上的索引（如果存在的话），必须执行全表扫描来逐一匹配符合条件的行。fetch next 10 rows only只是在所有符合条件的结果集生成后，截取前10条返回，但全表扫描的核心耗时已经产生，所以整体查询依然很慢。

缩短查询时间的优化方法 #

• 替换模糊查询为日期范围过滤：如果eff_date是日期类型，不要用字符串匹配，直接用日期函数限定范围，比如：

  select *
  from hourly_analysis 
  where eff_date between date '2018-01-01' and date '2018-01-31'
  fetch next 10 rows only
  

  这种写法能直接利用eff_date上的索引，避免全表扫描。如果eff_date是字符串类型，在业务逻辑允许的前提下，改用前缀匹配（'JAN-18%'），同样可以触发索引使用。
• 为eff_date创建索引：如果该字段尚未建立索引，执行以下语句创建普通索引：

  create index idx_hourly_eff_date on hourly_analysis(eff_date);
  

  注意确保查询方式能兼容索引（比如避免前缀通配符），否则索引无法生效。
• 避免select *，只查询必要字段：查询全量字段会增加数据读取和传输的开销，尤其是表中包含大字段（如CLOB、BLOB）时，只选择业务需要的列能大幅提升查询速度。
• 更新表统计信息：Oracle的执行计划依赖最新的表统计信息，如果统计信息过时，可能生成低效的执行计划。执行以下命令更新统计信息：

  exec DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'HOURLY_ANALYSIS');
  

• 考虑分区表改造：如果hourly_analysis是超大型表，按eff_date（如按月份）进行分区，查询时Oracle会仅扫描目标分区的数据，彻底避免全表扫描的开销。

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