Oracle11g分页查询性能低下 是否存在更高效的实现方案？

Oracle 11g分页性能优化方案 #

Oracle 11g原生不支持12c及以上版本推出的OFFSET ... FETCH NEXT简化分页语法，不存在完全无嵌套的单层分页查询写法，你遇到的性能问题本质和嵌套层数无关，是最内层查询的全量计算逻辑导致的。

问题根因

现有写法需要先将200万条符合过滤条件的记录完成所有关联计算、全量排序后，再截取前200条做分页，大量算力浪费在不需要的非目标数据计算上。

优化方案

• 优先实现索引覆盖排序
  将最内层查询WHERE条件用到的过滤字段、ORDER BY用到的排序字段联合创建索引，让Oracle可以直接通过索引顺序返回记录，跳过全表扫描、全量排序两个高开销步骤。例如过滤条件用到t.status=1、排序字段为t.create_time DESC，可创建联合索引idx_table_status_ctime(status, create_time DESC)，必要时可将关联主键也加入索引实现索引覆盖，避免回表查询。
• 改写SQL提前做主表分页截断
  不需要先完成所有关联再分页，先对主表做过滤、排序、分页，仅拿到目标页涉及的200条主表主键后再做关联操作，关联计算量直接从200万级降到百级，性能提升最明显。示例写法如下：

SELECT * FROM "TABLE_NAME" t
INNER JOIN X ON X.id = t.id
-- 剩余所有JOIN逻辑保持不变
WHERE t.id IN (
  SELECT id FROM (
    SELECT t_inner.id, rownum r__ FROM (
      SELECT id FROM "TABLE_NAME" t_inner
      WHERE ...... -- 仅保留主表的过滤条件
      ORDER BY xxx -- 排序逻辑提前到主表查询，利用索引快速取数
    ) t_inner
    WHERE rownum <= 200
  )
  WHERE r__ > 100
)
-- 最后仅对100条目标数据排序，无额外开销
ORDER BY xxx

• 验证执行计划
  执行EXPLAIN PLAN FOR 你的SQL语句检查执行计划，确认没有出现SORT ORDER BY全量排序步骤、已命中创建的联合索引，即表示优化生效。

如果业务场景允许分页出现极少量的重复或遗漏（如数据频繁新增删除的非核心场景），也可直接基于rownum做无嵌套的范围查询，但该方式存在数据一致性问题，不建议核心业务使用。

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