Oracle数据库中如何使用ROWNUM或FETCH FIRST实现高效结果集分页？

本文共计1022个文字，预计阅读时间需要5分钟。

ROWNUM 和 FETCH FIRST 是 Oracle 中实现分页的两条主要语句，但不能混用，也不能脱离数据库版本选型。Oracle 12c 之前只能依靠 ROWNUM 套子查询；12c 及以后版本应优先使用 OFFSET/FETCH FIRST，这并非因为更高级，而是因为它真正按语义执行——先排序、再跳行、最后截取。

为什么直接 WHERE ROWNUM BETWEEN 不生效

ROWNUM 是在结果集生成过程中动态赋值的，它永远从 1 开始、且只递增，不会跳号。所以 WHERE ROWNUM BETWEEN 11 AND 20 永远为空：第 1 行满足，ROWNUM 被赋为 1；第 2 行进来时，ROWNUM 已经是 2，不满足 BETWEEN 11 AND 20，直接被过滤掉，后续行根本没机会编号。

• 必须用两层子查询：内层先用 ORDER BY 排好序并限制上界（ROWNUM <= page * pageSize），外层再筛下界（用别名列如 r）
• 内层必须有 ORDER BY，否则排序不可控，分页结果会漂移
• ROWNUM 是伪列，不能在原始查询里直接 ORDER BY ROWNUM，它没有物理存储

FETCH FIRST 在 12c+ 中为何更可靠

OFFSET 和 FETCH FIRST 是标准 SQL:2008 语法，Oracle 12c 引入后严格按执行顺序处理： ORDER BY → OFFSET（跳过指定行）→ FETCH FIRST（取指定行）

• 支持负向偏移？不支持，OFFSET 必须是非负整数
• OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY 等价于不加 OFFSET，但显式写出来更利于参数化
• 如果 OFFSET 超出总行数（比如总共 100 行，OFFSET 200），查询不报错，返回空结果集 —— 这比 ROWNUM 方案中可能漏数据更可控

ROW_NUMBER() OVER 是不是万能替代方案

ROW_NUMBER() 窗口函数看起来灵活，但它和 ROWNUM 一样依赖子查询嵌套，且性能未必更好：

• 它必须配合 OVER (ORDER BY ...)，如果 ORDER BY 字段无索引，全表排序开销大
• 和 ROWNUM 方案相比，多了一次窗口计算，对大数据量页码靠后的场景（如第 10000 页），两种方案都会变慢，但 ROW_NUMBER() 多一层函数调用
• 它不能替代 FETCH FIRST 的语义简洁性：你仍需写 WHERE rn BETWEEN x AND y，而 FETCH FIRST 直接表达意图

实际写法中几个硬性避坑点

- OFFSET 参数不能为负，传参前务必校验：Math.max(0, (page - 1) * pageSize) - 使用 ROWNUM 时，内层 SELECT 的 ORDER BY 必须存在，且字段最好有索引，否则每次分页都触发全表排序 - FETCH FIRST n ROWS ONLY 中的 n 不能是变量（如 :pageSize），必须是字面量或绑定变量 —— 但绝大多数驱动（JDBC/ODBC）支持绑定，不用拼字符串 - Oracle 12.1.0.1 有个已知 bug：当 OFFSET 非常大（如 > 100 万）且未走索引时，优化器可能选择错误执行计划，此时回退到 ROW_NUMBER() + 覆盖索引更稳

分页不是“写出来就行”，关键是让数据库按你设想的顺序和范围交出数据。版本判断必须前置，FETCH FIRST 看似简单，但没意识到它要求 ORDER BY 存在且高效，就容易在线上查着查着变慢；而死守 ROWNUM 套路，又会在升级到 19c 后错过优化器对 OFFSET 的物化视图友好支持。