Oracle 分页这个问题，我在刚转过来做 Oracle 的时候被折磨得不轻。那时候从 MySQL 过来的人，脑子里全是LIMIT ? OFFSET ?，到了 Oracle 发现根本不认这套，官方文档翻半天也没找到一个跟 MySQL 一模一样的用法。后来我才搞清楚，Oracle 之所以分页 SQL 写得绕，根源就在它那个独特的ROWNUM机制上。这篇就把我在实际项目里用到的几种 Oracle 分页写法、优化思路、还有 MyBatis-Plus 分页失效这些坑，一次性讲透。
1. 为什么 Oracle 分页这么绕：先搞懂 ROWNUM 再写 SQL
1.1 ROWNUM 的本质：取一行编一个号
很多第一次接触 Oracle 分页的人，最容易踩的坑就是直接写WHERE ROWNUM > 100，然后惊讶地发现一条数据都查不出来。这不是 Bug，而是 ROWNUM 的分配时机问题。
ROWNUM 是 Oracle 在查询结果返回之前，给每一行赋予的序号。关键在于：这个序号是"取一行，编一个号"，不是先取完所有行再统一编号。当执行WHERE ROWNUM > 100时，Oracle 取出第一行，给它编上 1，发现 1 > 100 不成立，直接丢弃；再取第二行，编上 1，又不成立，又丢弃。就这样所有行都被过滤掉了。
所以记住一个结论：ROWNUM 只支持<=这种从 1 开始的条件，不支持> N和BETWEEN直接作为分页条件。正是因为这个限制，Oracle 分页 SQL 才必须采用"先取范围，再截断，再套外层"的嵌套写法。
1.2 理解执行顺序比死记模板更重要
Oracle 的 SQL 执行顺序大致是：FROM → WHERE → GROUP BY → HAVING → SELECT（此时分配 ROWNUM） → ORDER BY。这里有最关键的一点：ORDER BY在 ROWNUM 分配之后才执行。
这带来一个经典问题：如果你在子查询里直接写WHERE ROWNUM <= 10 ORDER BY create_time DESC，得到的结果并不是"按时间排序后的前 10 条"，而是"前 10 条数据再按时间排个序"。这也是新手写 Oracle 分页最常见的错误。
正确的思路必须是：先让排序发生，再分配 ROWNUM。所以标准的 Oracle 分页模板把排序放在最内层，ROWNUM 限制放在外层子查询，最终再包一层做起始位置的过滤。
1.3 三层嵌套的模板是怎么来的
Oracle 分页经典写法是三层嵌套：
SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY empno ) t WHERE ROWNUM <= 20 ) WHERE rn > 10;拆开看就很清晰了：
- 最内层子查询
t：负责排序，把业务排序逻辑先做掉； - 中层子查询：此时执行到
WHERE ROWNUM <= 20，Oracle 会取到前 20 行并分配连续的 ROWNUM 序号，这层干的是"截断"的活； - 最外层：通过
WHERE rn > 10过滤掉前 10 行，得到第 11 到 20 条。
这个模板解决了两件事：一是绕开了ROWNUM不能直接大于某值的问题，二是保证了排序逻辑先于行号分配执行。理解了这套逻辑，你再看网上各种分页写法，就能一眼判断哪些是对的、哪些是有问题的。
2. 三种主流 Oracle 分页写法与适用场景
2.1 经典 ROWNUM 嵌套：全版本通用
上面那套三层嵌套，就是兼容性最好的写法。不管你是 10g、11g 还是 19c，只要能跑 SQL 就能用。实际项目中我一般会把页码和每页大小抽成变量，写成这样：
SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY empno ) t WHERE ROWNUM <= #{pageNum} * #{pageSize} ) WHERE rn > (#{pageNum} - 1) * #{pageSize};注意这里中层子查询的ROWNUM <= pageNum * pageSize，是"当前页的截止行号"，外层再用起始行号做过滤。比如第 2 页、每页 10 条，中层先取前 20 条，外层去掉前 10 条，剩下 11 到 20 条。
这套写法有个细节要留意：内层排序字段一定要有唯一性。如果排序字段大量重复，比如按status排，Oracle 每次执行时相同排序值的行顺序可能不稳定，分页就会出现"上一页最后一条和下一页第一条重复"或者"丢数据"的情况。稳妥的做法是在排序字段末尾追加主键，比如ORDER BY create_time DESC, id DESC。
2.2 ROW_NUMBER() 窗口函数：排序稳定的另一种选择
Oracle 9i 以后支持分析函数，分页可以换个思路：
SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY create_time DESC, id DESC) rn FROM emp t ) WHERE rn BETWEEN 11 AND 20;这种写法的优势是：ROW_NUMBER()在同一个查询里就能完成排序和编号，代码语义更清晰。它跟 ROWNUM 最大的区别是：ROW_NUMBER()是分析函数，在排序完成后统一赋号，因此可以直接用BETWEEN取中间任意区间。
但这套写法在超大数据量下性能不一定比经典 ROWNUM 写法好，因为ROW_NUMBER()会把全量结果都排完再编号，而经典 ROWNUM 写法在中层就截断了，CBO 有机会做更多优化。我的建议是：数据量小、SQL 逻辑复杂的场景用 ROW_NUMBER() 更清晰；数据量大、追求性能的场景用经典 ROWNUM 嵌套。
另外说一句，ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)这种带分组的分页我平时不大用，除非业务明确需要每个分组分别取前 N 条，否则分区反而会把分页语义搞复杂。
2.3 OFFSET FETCH：12c 以后的新选择
如果你的数据库版本是 12c 及以上，直接用 ANSI 标准的OFFSET FETCH：
SELECT * FROM emp ORDER BY create_time DESC, id DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;OFFSET 10表示跳过前 10 条，FETCH NEXT 10 ROWS ONLY表示取后面 10 条。这套语法跟 MySQL 的LIMIT 10, 10语义非常接近，可读性最好。而且 Oracle 12c 之后对OFFSET FETCH的优化器做了专门支持，在普通翻页场景下性能并不逊色于经典 ROWNUM 写法。
但有几个坑要提醒一下。
- 一是
OFFSET FETCH必须搭配ORDER BY，不然结果集顺序不确定； - 二是 12c 以下的版本完全不支持这套语法，如果系统里还挂着 11g 的实例，DBA 给你权限也白搭，写了直接报
ORA-00933； - 三是这种写法同样存在深分页问题，翻到几百页之后一样会慢，它只是写起来方便，不是性能银弹。
2.4 三种写法究竟怎么选
我整理了一份选型表，照着选基本不会出大错。
| 写法 | 版本要求 | SQL 复杂度 | 深分页性能 | 适用场景 |
|---|---|---|---|---|
| ROWNUM 嵌套 | 所有版本 | 较高 | 较好 | 生产环境默认首选，特别是大表 |
| ROW_NUMBER() | 9i+ | 较低 | 一般 | 逻辑复杂、分页区间不固定的小表 |
| OFFSET FETCH | 12c+ | 最低 | 一般 | 新项目、内部管理系统、版本允许 |
在我维护的老系统里，11g 的业务我几乎全用经典 ROWNUM 写法；新项目如果是 19c，我反而会优先用OFFSET FETCH，不是为了炫技，纯粹是代码更好维护，后来接手的同事不用再背着三层嵌套的 SQL 一遍遍问"这每层是干嘛的"。
3. 分页性能优化的核心：怎么跳出"越翻越慢"的魔咒
3.1 为什么翻到后面，SQL 越来越慢
不管用上面哪种写法，只要翻页页数一深，数据库都会变慢。很多刚接触 Oracle 的同学跑一条深分页 SQL，看执行计划发现已经命中索引了，但还是慢得离谱，然后就开始怀疑是不是索引写错了。
其实原理很简单。数据库分页是"物理位置"扫描，不是"逻辑跳转"。你要取第 100000 到 100010 条，数据库也必须从那排好序的结果集的第一条开始数，一路数到 100000 条以后才能拿到你要的那 10 条。前 100000 行数据全部都要经过排序、比较、丢弃的过程，这部分开销一分不少。所以深分页慢是必然的，不是 SQL 写错了。
这种情况在OFFSET FETCH和 ROWNUM 嵌套里都存在，因为这两种方式本质都是"偏移"分页。
3.2 用"上一页最后一条"做条件：键集分页
深分页优化最有效的手段，是用上一页返回的最后一条记录的排序字段值，来做下一页的起始条件。这种方案在业界叫"键集分页"（Keyset Pagination），也叫 seek 分页。
举个例子，你按create_time DESC, id DESC排序，每页 10 条。上一页最后一条记录的create_time = '2024-06-01 12:30:00'，id = 10086，那么下一页的 SQL 应该写成：
SELECT * FROM emp WHERE (create_time, id) < ('2024-06-01 12:30:00', 10086) ORDER BY create_time DESC, id DESC FETCH NEXT 10 ROWS ONLY;或者写成等价的元组比较形式，Oracle 11g 里也可以用两个独立条件拼出来：
WHERE create_time < '2024-06-01 12:30:00' OR (create_time = '2024-06-01 12:30:00' AND id < 10086)这种写法为什么快？因为它把"数 10 万行再丢弃 99990 行"变成了"直接通过索引定位到 10 万行之后的那个位置，然后继续往后读 10 行"。索引一跳就到位了，扫描量从 O(N) 降到了接近 O(页码大小)。
代价也很明确：你不能直接跳页，只能一页一页往后翻。所以适合的场景是"瀑布流加载""列表无限滚动""用户极少跳页"这类业务。如果你的业务强制要求用户点第 800 页，那没辙，只能承受深分页开销，或者提前把总数和汇总数据算好缓存起来。
3.3 延迟关联：先取主键再回表
还有一种我经常搭配使用的优化技巧叫"延迟关联"，思路是避免在排序大字段和多余列上浪费 IO。
比如表里有一大堆 TEXT/CLOB 字段，你要是直接SELECT *去分页排序，数据库得把这些大字段全部捞出来参与排序，IO 和临时表空间消耗都非常大。改成先只查主键和排序字段：
SELECT * FROM emp e INNER JOIN ( SELECT id FROM emp ORDER BY create_time DESC, id DESC OFFSET 100000 ROWS FETCH NEXT 10 ROWS ONLY ) t ON e.id = t.id ORDER BY e.create_time DESC, e.id DESC;内层查询只需要访问索引，就能完成排序和偏移，数据量小很多；外层再拿 10 个主键回表取完整数据。这样大字段的读取量从 10 万行压缩到了 10 行，性能提升非常明显。
这个技巧跟"非分页缓冲池占用很高"这类内存问题是同一套治理思路：用更少的逻辑读完成同样的业务。我在项目里排查慢 SQL 时，遇到分页相关的高逻辑读 SQL，第一反应就是检查是不是SELECT *带着一堆不用的列去排序。
3.4 分页总数(count)的优化
分页 SQL 里还有一个容易被忽略的性能炸弹，就是SELECT COUNT(*)。很多框架自动生成的 count 语句会跟着一大堆条件子查询，在大表上 count 一次可能就是秒级。
如果业务对分页总数要求不高，可以考虑用近似值替代精确值，比如"总条数超过 1000 时显示 1000+"。这是我做列表页常用的策略，避免每次都跑一次精确 count。
另外要留意 count 执行的时机，最好只在第一页加载时 count 一次，然后缓存一小段时间，而不是每次翻页都重新 count。
4. 从 SQL 到框架：MyBatis-Plus 分页失效的那些坑
4.1 分页插件为什么没生效
Java 后端用 MyBatis-Plus 配 Oracle 分页，我遇到过太多"明明配了分页插件，SQL 却把全表数据都查出来了"的情况。Point 基本集中在几个地方。
第一，分页插件没被注册到 SqlSessionFactory。如果你自定义了MybatisSqlSessionFactoryBean或者用多数据源框架，很容易出现新创建的 sessionFactory 并没有把PaginationInterceptor/MybatisPlusInterceptor装进去。这种情况表现就是：调用Page参数确实传了，但最终执行时 SQL 没有被拦截改写。
第二，新版 API 变了。MyBatis-Plus 3.4.0 之前用的是PaginationInterceptor，3.4.0 之后换成了MybatisPlusInterceptor里面套PaginationInnerInterceptor。很多老博客还在抄旧代码，版本不对直接 NoSuchMethodError 或者插件静默失效。
正确的配置方式大概是：
@Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor(DbType.ORACLE); paginationInterceptor.setOverflow(false); paginationInterceptor.setMaxLimit(500L); interceptor.addInnerInterceptor(paginationInterceptor); return interceptor; }这里特别注意DbType.ORACLE一定要按实际数据库版本设置，填错了 MySQL 方言去生成 Oracle SQL，一样会翻车。
第三，COUNT 查询和主查询拼接了FOR UPDATE。MyBatis-Plus 分页插件遇到FOR UPDATE会做特殊处理，但如果 SQL 写得太复杂，比如包含自定义 JOIN、UNION，插件生成的 count 语句就可能不准甚至报错。这时候我一般会手动提供一个 count SQL 覆盖它的默认行为。
4.2 分页插件在 Oracle 上到底做了什么
MyBatis-Plus 在 Oracle 方言下生成分页 SQL，本质就是帮你包一层 ROWNUM 子查询。原理参考我之前写的那套三层嵌套，框架只是在 SQL 执行前用拦截器把原 SQL 包成了：
SELECT * FROM ( SELECT TMP.*, ROWNUM ROWNUM_ FROM (原SQL) TMP WHERE ROWNUM <= ? ) WHERE ROWNUM_ > ?理解了这个原理，你排查"分页插件生成 SQL 慢"的问题就会有一个很直观的方向：先看看框架包出来的 SQL 是否让 Oracle 走了全表排序，再对照原 SQL 的执行计划分析哪一层是瓶颈。
4.3 分页插件的性能成本：临时表和 count 双开销
分页插件在 Oracle 上还有个隐藏成本，就是每页查询可能产生临时表排序，特别是当原 SQL 里带着复杂关联时。我见过一个报表列表接口，主查询本身就要关联五张表，分页插件每次执行时 Oracle 都得生成一个巨大的中间结果集，再排序分页，接口响应直接飙到 5 秒以上。
这种场景下，框架生成的 count 和分页 SQL 都很笨重。我的处理办法是：放弃自动分页，手写针对性的分页 SQL。把五张表的关联结果先做一次物化，或者把核心业务表拆出去先分页再关联，效果立竿见影。这个思路其实就是我上面说的"延迟关联"在框架层的变体。
4.4 分页参数的安全问题
分页参数一般是pageNum和pageSize，很多同学直接用#{pageNum}传参，这个没问题，MyBatis 的预编译机制能防注入。但就怕有人图省事，把排序字段拼成${orderBy}动态拼接进 SQL，这就留下了一个注入点。
我在代码审查时碰到过把pageSize直接拼进 SQL 的情况，服务端没做大小限制，最后被攻击者传了个pageSize=99999999把整表拖走。分页参数一定要做兜底：pageNum 不能小于 1，pageSize 要设上限，排序字段要白名单校验。这不是 SQL 写法问题，是安全意识问题。
5. 常见问题与排查技巧实录
5.1 分页结果出现重复或丢失
症状：翻页后和上一页有重复数据，或者某些数据一直翻不到。
原因九成是排序字段不唯一。MySQL 里有类似问题，Oracle 也一样。处理方案是给ORDER BY追加一个唯一字段，常见做法是加主键id DESC收尾。
还有一种情况是翻页期间正好有数据插入或删除，比如用户翻到第 3 页时，第 1 页插入了一条新数据，所有行的整体位置后移，第 3 页自然就会跟第 2 页有重复。这个属于"翻页期间数据变化"导致的业务层问题，不是 SQL 的问题，通常通过键集分页或者快照读思路来规避。
5.2 ROWNUM 排序错乱
我见过有人写成这样：
SELECT * FROM ( SELECT ROWNUM rn, e.* FROM emp e ) WHERE rn BETWEEN 11 AND 20 ORDER BY create_time DESC;看起来有排序，其实内层SELECT ROWNUM, e.* FROM emp没有排过序，ROWNUM 是按物理存储顺序分配的，最后一层再排序只是把取到的 10 条结果排了一下，整个分页结果完全乱掉。这种 SQL 我建议直接重写为经典三层嵌套模板。
判断"排序是否生效"，最直观的方法是打印执行计划，看有没有SORT ORDER BY出现在 ROWNUM 分配之前。
5.3 深分页导致临时表空间暴涨
如果分页 SQL 排序的数据量太大，Oracle 会使用临时表空间做排序溢出。有一次线上告警临时表空间使用率接近 100%，排查下来就是某个报表接口被定时任务调用，一次性往后翻了几百页，每次都要对百万级数据排序。
处理这类问题我一般分三步：第一步限制单页大小和最大页码；第二步给排序字段建组合索引，让排序尽量走索引避免 sort 溢出；第三步实在压不下去，就改成键集分页，让每次查询都只访问索引的一小段。这套组合拳打下来，临时表空间压力能降一大截。
5.4 分页慢 SQL 快速排查清单
我把日常排查分页慢 SQL 的套路整理成了一张清单，遇到问题直接对照着过一遍：
- 执行计划里有没有
SORT ORDER BY？如果有，确认排序字段是否有索引支撑。 ROWNUM分配的位置对不对？重点看 ROWNUM 是在排序前还是排序后被截断。- 分页 SQL 是不是
SELECT *带着大字段？如果是，试一下先分页主键再回表。 - count 查询是不是在反复执行？是否缓存过多页总数？
- 是否用了框架自动分页，而主 SQL 本身是超复杂 JOIN？如果是，考虑手写分页。
- 看到
TABLE ACCESS FULL了吗？如果全表扫描还伴随排序，基本是必慢的。
这套清单我在团队里分享过很多次，也能用来给新人上手 Oracle 分页优化时当检查手册用。
6. 结合场景的取舍心得：别让分页写法绑架了业务
Oracle 分页归根结底是"用排序键换查询范围"的问题，不同的业务形态适合不同的方案。比如内部管理系统，数据量也就几万条，用最稳妥的三层嵌套就够；C 端用户中心的订单列表，数据到了百万级，我强烈建议一步到位用键集分页，别等线上报警了再改。
还有一个小建议：在代码里尽量把分页 SQL 封装成统一的 DAO 层方法，不要每个接口各写各的。因为将来换数据库或者升级版本的时候，你会发现全部要改的 SQL 集中在几个文件里，远比满项目搜分页关键字要省事。
最后再分享一个小经验：用 Oracle 分页，别只看第一页快不快，一定要拿最后一页最坏情况测。很多分页 SQL 第一页秒回，翻到第 1000 页直接超时，问题不是这时候才出现的，而是一开始设计时就埋下了。我们在压测时专门设计了一条"翻到最深页"的用例，专门锤分页性能，比随机翻页测试有效得多。
