Oracle百万级数据分页查询的索引策略选择与性能优化咨询

最近帮人排查过类似的问题：当Oracle表数据量冲到近百万甚至数百万行时，分页查询的性能直接跳水，慢到让人崩溃。翻了Oracle官方文档，再结合执行计划的分析，终于找到根儿上的问题了——Fast Full Index Scan没有利用已有的排序索引，导致每次查询都要对全量数据重复排序。

给你说个实际测试的场景：

• 一开始表数据量不大的时候，查前几行的执行计划显示是Full Index Scan，这时候能顺着已排序的索引直接取数，速度贼快；
• 但往表里插了几千行新数据，重新收集统计信息之后，执行计划直接变成了Fast Full Index Scan——这玩意儿不管索引的顺序，直接扫整个索引，然后再做一次全局排序，分页的效率瞬间就崩了，数据量越大，这个问题越明显。

为啥会出现这种情况？ #

Oracle的CBO（成本优化器）是根据统计信息来选执行计划的。当数据量变大后，CBO可能觉得Fast Full Index Scan的I/O成本更低，但它没算上排序带来的额外开销——毕竟分页场景下，我们只需要前N条有序数据，全量排序纯纯是做无用功。

亲测有效的解决办法 #

给你几个我试过能用的方案：

• 强制指定有序索引扫描：在查询里加hint提示，告诉CBO直接用建好的排序索引，别搞Fast Full那套。比如：

  SELECT /*+ INDEX(your_table idx_your_sorted_col) */ *
  FROM (
      SELECT t.*, ROWNUM rn
      FROM your_table t
      ORDER BY sorted_column
  )
  WHERE rn BETWEEN 1 AND 20;
  

• 校准统计信息：如果重新收集统计信息后CBO判断错了，可以手动做全量统计，让CBO更准确地评估成本。执行这条命令：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => '表名', ESTIMATE_PERCENT => 100);
  

• 换成分页专用的SQL写法：别用OFFSET（Oracle 12c及以上支持），改用基于排序字段的范围查询，用上一次分页的最后一条数据的排序值当条件，直接定位下一页，完全跳过全量排序：

  SELECT *
  FROM your_table
  WHERE sorted_column > :last_page_max_value
  ORDER BY sorted_column
  FETCH FIRST 20 ROWS ONLY;
  

内容的提问来源于Stack Exchange，提问作者danijepg