做开发这些年Oracle分页查询算是被问得最多的基础问题之一。很多从MySQL转过来的同事上手就习惯找LIMIT结果在Oracle里直接报错一脸懵。Oracle没有MySQL那种LIMIT语法查了文档会发现官方压根没给一个简单的分页关键字所以社区里流传着各种写法。我梳理下来真正实用、值得掌握的其实就三种ROWNUM伪列嵌套、ROW_NUMBER()分析函数以及12c以后新增的OFFSET FETCH。这篇就把这三种方法从原理到实战一次性讲透顺便把大表分页的性能优化和日常踩坑也一起聊了不管你是做ERP二次开发、报表系统还是写存储过程这几套写法都够用。1. 三种分页方法概览与选型思路1.1 三种写法速览先把三种写法的骨架摆在这里后面再逐个拆原理。假设有个员工表emp字段包括empno、ename、sal现在要看第3页每页10条数据按empno升序排序。ROWNUM三层嵌套写法这是最原生、兼容性最好的一套SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT e.* FROM emp e ORDER BY e.empno ) t WHERE ROWNUM 30 ) WHERE rn 21;ROW_NUMBER()分析函数写法逻辑上更直白SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY e.empno) AS rn FROM emp e ) WHERE rn BETWEEN 21 AND 30;OFFSET FETCH写法Oracle 12c及以上版本才有SELECT * FROM emp e ORDER BY e.empno OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;这三种方法应对日常分页查询都绰绰有余区别主要在于版本兼容性、SQL可读性和深分页时的性能表现。我在实际项目里老系统11g的库基本固定用第一种12c以后的新项目代码审查时我更推荐第三种至少新人接手时不用对着三层嵌套发愣。1.2 选型原则与版本约束选哪种写法第一决定因素是数据库版本。ROWNUM和ROW_NUMBER()在Oracle 9i以后就全版本支持可以说是老库的救命稻草。OFFSET FETCH是12c才引入的标准语法你要在11g上写直接给你报ORA-00933: SQL command not properly ended。写法Oracle版本要求可读性深分页性能适用场景ROWNUM三层嵌套全版本一般较好有上界截断11g及以下老库、存储过程ROW_NUMBER()9i较好较差需全量编号复杂统计中顺便编号OFFSET FETCH12c好一般深度偏移也会慢新项目、标准SQL偏好另外还得考虑团队技术栈。如果项目里大量用MyBatis这类框架很多分页插件能自动把方言翻译成对应数据库的分页语句你写的业务SQL反而不需要关心这些。但一旦涉及报表、接口直出SQL或者存储过程动态拼接自己心里必须清楚底层生成的是哪种写法。1.3 分页参数换算公式无论是哪种写法底层都是两个参数当前页码page、每页条数pageSize。需要先换算出起始行号和结束行号起始行号 startRow (page - 1) * pageSize 1比如第3页每页10条startRow 21结束行号 endRow page * pageSize也就是 3 * 10 30ROWNUM写法里内层WHERE ROWNUM 30用于截断外层WHERE rn 21用于定位。OFFSET FETCH里OFFSET 20表示跳过前20行对应startRow - 1FETCH NEXT 10对应pageSize。这套换算在任何语言写代码时都通用务必记牢。2. ROWNUM伪列最经典的三层嵌套写法2.1 ROWNUM伪列到底是怎么工作的ROWNUM是Oracle特有的伪列它在查询执行阶段给结果集的每一行动态分配一个序号从1开始递增。这里最关键的点在于这个序号是边取行边分配的并不是表里存的真实字段。很多新手上来就写WHERE ROWNUM BETWEEN 21 AND 30结果发现一条数据都查不出来特别困惑。原因是ROWNUM是在WHERE条件过滤之前就参与计算的。数据库从表里读第一行时给它的ROWNUM是1然后判断ROWNUM BETWEEN 21 AND 301不满足这一行被丢弃。接着读第二行又从头分配为1还是不满足再丢弃。如此循环永远没有行能拿到大于等于21的ROWNUM因为不满足条件的行会不断把序号重置回起点。所以ROWNUM的使用规律是可以用ROWNUM N或者ROWNUM 1但绝不能直接写ROWNUM N或者ROWNUM BETWEEN N AND M。这就是为什么需要三层嵌套先让ROWNUM在子查询里把上界截断住把编号固化下来再到外层做下界过滤。2.2 三层嵌套的标准模板与参数计算三层嵌套的每一层都有自己的使命-- 最内层排序 SELECT e.* FROM emp e ORDER BY e.empno -- 中间层生成行号并用ROWNUM截断到endRow SELECT t.*, ROWNUM AS rn FROM ( SELECT e.* FROM emp e ORDER BY e.empno ) t WHERE ROWNUM 30 -- 最外层过滤起始行得到最终分页结果 SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT e.* FROM emp e ORDER BY e.empno ) t WHERE ROWNUM 30 ) WHERE rn 21;中间层里SELECT t.*, ROWNUM AS rn的写法有个细节ROWNUM是在WHERE ROWNUM 30这个条件判断之前还是之后分配很多人理解有偏差。实际上Oracle是先给行分配ROWNUM再做ROWNUM 30判断通过就保留不通过就丢弃并继续处理下一行。所以最终保留下来的一定是前30行且rn是连续递增的1到30。我平时写存储过程或动态SQL时会直接用绑定变量把startRow和endRow传进来中间层只截断到endRow最外层过滤startRow这样通用性很强SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT e.empno, e.ename, e.sal FROM emp e ORDER BY e.empno ) t WHERE ROWNUM :endRow ) WHERE rn :startRow;这样无论前端传page还是pageSize代码里换算好startRow和endRowSQL模板完全不用动。2.3 排序稳定性与唯一性问题ROWNUM分页最大的隐形坑是排序字段不唯一带来的分页错乱。比如按员工的入职日期HIREDATE排序同一天入职的人可能很多Oracle对相同排序值的行返回顺序是不确定的第一页拿到的可能是张三第二页刷新后可能变成了李四结果就是同一行数据在相邻两页重复出现或直接丢失。解决办法很简单ORDER BY里加一个绝对唯一的字段做第二排序键。生产环境里我一般直接加主键SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT e.* FROM emp e ORDER BY e.hiredate, e.empno ) t WHERE ROWNUM :endRow ) WHERE rn :startRow;这里还有一个关于NULL排序的细节。Oracle默认排序规则是升序时NULL值排最后降序时NULL值排最前。如果排序字段允许为NULL且业务上需要NULL固定排最后建议显式加上NULLS LAST避免不同版本或不同索引情况下行为不一致ORDER BY e.commission DESC NULLS LAST2.4 ROWNUM写法的常见变形ROWNUM除了分页还能顺带解决取前N行的场景写法就是两层嵌套SELECT * FROM ( SELECT e.* FROM emp e ORDER BY e.sal DESC ) WHERE ROWNUM 10;很多人不知道的是ROWNUM分页可以和总数统计合并到一条SQL里。报表系统经常既要当前页数据又要总条数传统做法是跑两条SQL一条COUNT一条查数据。数据量不大时没问题但大表场景下COUNT全表扫描代价不低。可以利用分析函数COUNT(*) OVER()在查询结果集内统计总行数一次扫描同时拿到数据和总数SELECT * FROM ( SELECT t.*, ROWNUM AS rn, COUNT(*) OVER() AS total_count FROM ( SELECT e.* FROM emp e ORDER BY e.empno ) t WHERE ROWNUM :endRow ) WHERE rn :startRow;total_count拿到的就是满足排序前条件的总记录数不需要额外跑COUNT。注意这个总数是整个排序结果集的行数不是当前页的行数语义正好符合分页需求。不过它也有局限如果总数据量是百万级COUNT(*) OVER()仍然要把所有符合条件的行都算一遍性能并不会比单独跑COUNT好太多小表和中等数据量的报表场景用着很舒服超大数据集还是老老实实单独做统计或者走缓存。3. ROW_NUMBER()分析函数写法3.1 原理与标准写法ROW_NUMBER()是Oracle 9i就引入的分析函数作用是在结果集的分组PARTITION BY内按指定排序规则给每一行分配一个连续递增的行号。分页场景下不写PARTITION BY就是全局编号。标准写法是先排序并编号外层再按行号范围过滤SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY e.empno) AS rn FROM emp e ) WHERE rn BETWEEN :startRow AND :endRow;语法上比ROWNUM三层嵌套清爽不少排序逻辑直接写在OVER子句里不用再专门包一层排序子查询。而且行号是在整个排序完成之后分配的所以可以直接用BETWEEN同时过滤上下界不用担心ROWNUM那种一边分配一边丢弃的死循环问题。3.2 适用场景与性能注意虽然ROW_NUMBER()语法好懂但它有个性能短板Oracle需要先把所有匹配条件的行按排序键排好再为每一行计算编号最后才在外层过滤行号范围。对比ROWNUM写法中间层WHERE ROWNUM endRow这个条件优化器可以尽早停止读取数据数据量越大两者差距越明显。比如一张千万级的大表取第100万页的数据ROWNUM写法扫描到第100万条就不再往后读而ROW_NUMBER()很可能要把全表都参与排序编号。所以在生产环境我不建议把ROW_NUMBER()作为大表深分页的首选方案。它更适合以下几种场景需要同时取出多条排序规则的编号比如既要按工资排名又要按入职时间排名在复杂统计SQL中顺便计算排名和行号而不是单纯做分页分页的数据集本身已经很小比如经过WHERE条件过滤后只剩几百行如果确实要用ROW_NUMBER()做分页并且查询条件能过滤掉大量数据性能也没那么差。毕竟先过滤再编号参与排序的行少了速度自然就上来了。关键要看执行计划不能一概而论。3.3 在复杂查询里的妙用先编号再去重ROW_NUMBER()一个很经典的使用场景是配合PARTITION BY去重。比如订单表和订单明细表一对多关联你要按主表订单分页但明细表可能一个订单有多条记录直接join再分页会导致同一订单出现在不同页里。我遇到这种情况的处理方式是先用ROW_NUMBER()对主表订单编号然后只取rn 1的行作为分页主表再关联明细。示例SELECT od.* FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY o.order_id) AS rn FROM orders o ) od JOIN order_lines ol ON ol.order_id od.order_id WHERE od.rn 1 AND od.order_id BETWEEN :startId AND :endId;这套路在处理主从表分页时非常好用ROWNUM写法反而没那么直观。所以别把ROW_NUMBER()一棍子打死分页只是它的众多用法之一关键场景下它比ROWNUM灵活得多。4. Oracle 12c 的OFFSET FETCH写法4.1 语法与三种分页写法对照Oracle 12c终于引入了标准的OFFSET FETCH子句拿不到还是先报错语法上和其他数据库的LIMIT非常接近。基本格式SELECT 列 FROM 表 ORDER BY 排序列 OFFSET 起始行数 ROWS FETCH NEXT 每页行数 ROWS ONLY;拿第3页每页10条来说OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY。这个写法最大的好处是可读性好一眼就能看懂要跳过20行取10行。和MySQL对比一下就理解了MySQLLIMIT 20, 10PostgreSQLLIMIT 10 OFFSET 20Oracle 12cOFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY语义完全对应。我碰过不少从MySQL转过来的团队在12c以上的库里用OFFSET FETCH几乎零学习成本。4.2 版本限制与替代方案OFFSET FETCH最大的坎是版本。Oracle 11g及以下完全不认识这个语法ORA-00933报错能劝退一堆人。如果你的生产库是11g又确实喜欢这种语义清晰的写法只能退回到ROWNUM三层嵌套。这里有个不算技巧的技巧写一套公共的分页SQL生成器根据数据库版本决定走OFFSET FETCH还是ROWNUM这样上层业务不用改。还有一种情况12c以上版本OFFSET FETCH写在子查询里也要注意ORACLE的优化器行为。我遇到过明明只是取第2页20条数据执行计划却显示对全表做了排序。原因是OFFSET值较小时问题不大但OFFSET很深时Oracle同样要先扫描并丢弃前OFFSET行它的执行成本随着偏移量线性增长。这和ROWNUM写法有上界截断的优化路径不同OFFSET FETCH在深分页场景下不一定比ROWNUM快需要实际测试。4.3 其他选项FIRST、NEXT、PERCENT和WITH TIESOFFSET FETCH还支持几个不常用但值得一提的选项。FETCH FIRST和FETCH NEXT是等价的只是语法风格不同ROWS和ROW也是单复数的区别不影响执行。PERCENT关键字可以按百分比取行比如FETCH FIRST 10 PERCENT ROWS ONLY意思取结果集前10%的行。WITH TIES比较特殊它会把排序值跟最后一行相同的额外记录也带出来防止因为截断导致并列排名的数据被切掉。-- 取工资最高的前10人并列第10名也包含进来 SELECT empno, ename, sal FROM emp ORDER BY sal DESC FETCH FIRST 10 ROWS WITH TIES;这种语义在很多报表场景里非常有用。不过要提醒一句OFFSET和WITH TIES同时用时并列判断是基于最终页面边界的不是整个结果集稍不注意容易理解偏差实际用的时候建议多跑几条数据验证。5. 大表分页的性能优化实践5.1 深分页为什么会慢先抛出一个结论任何数据库的分页越往后翻越慢这是物理规律不只是Oracle的问题。原因也不复杂数据库并不知道你要取第100万页它只能从头开始数把前面所有行都找出来、跳过再返回你真正要的那一批。OFFSET值越大需要丢弃的行越多自然越慢。ROWNUM写法因为有WHERE ROWNUM endRow的上界Oracle能做一定的提前终止优化但前提是排序本身有索引支撑。如果ORDER BY的字段上没有索引数据库还是得把全表数据都捞出来排序再截断前endRow行代价一点不少。所以大表分页性能优化的核心不是纠结用哪种分页语法而是让排序截断这两个动作尽量轻量。5.2 延迟关联先拿主键再回表分页查询最怕的事情之一是SELECT后面跟着一大堆字段包括大字段、CLOB、几十个列同时又对全表做排序。每行数据都占很大空间排序和扫描的成本直线上升。延迟关联的思路是分页子查询里只查排序字段和主键ID拿到当前页的ID集合后再回原表取完整行避免排序阶段就拖着所有的大字段跑。示例SELECT e.* FROM emp e JOIN ( SELECT empno FROM emp ORDER BY empno OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY ) t ON t.empno e.empno ORDER BY e.empno;这样内层子查询只需要访问索引覆盖索引即使偏移量很大参与排序的每条记录也只是很小的键值对整体I/O大幅下降。外层再通过主键关联取详情。实测在百万级表上同样的深分页延迟关联能比直接SELECT *快几倍到十几倍具体取决于行宽和列的多少。11g环境下把OFFSET替换成ROWNUM写法同样适用这套优化思路。5.3 键集分页适合下一页场景的终极方案如果业务场景是上一页/下一页而不是跳转到第N页那么最优雅的做法是键集分页也叫游标分页或Seek Method。原理很简单记住上一页最后一条记录的排序键值下一页直接用WHERE条件从该键值往后取。比如按empno排序第一页取到最大empno是10020第二页的SQL就是SELECT * FROM emp WHERE empno 10020 ORDER BY empno FETCH FIRST 20 ROWS ONLY;11g下用ROWNUM写法SELECT * FROM ( SELECT * FROM emp WHERE empno 10020 ORDER BY empno ) WHERE ROWNUM 20;这套方案的好处是不管翻到多深的页数据库都只从指定键值开始扫描扫描行数恒等于每页行数性能极其稳定。我在移动端下拉加载和业务系统逐页审核场景中基本都用这个方案。但它有两个限制需要注意一是排序键必须唯一如果不唯一还是会出现重复或漏数据所以常用主键或唯一索引列二是无法直接跳页因为缺少上一页末尾这个游标就没法定位。如果能接受这两点键集分页是处理海量数据分页的最佳选择。5.4 索引与排序字段的配合分页SQL能不能走索引是性能的另一大关键点。如果ORDER BY的字段上有合适的索引Oracle可以直接按索引顺序扫描跳过排序环节。比如ORDER BY empno在empno是主键的情况下索引天然有序ROWNUM写法和OFFSET FETCH都能很好地利用这个特性。如果是多条件排序比如ORDER BY hiredate, empno可以建一个联合索引(hiredate, empno)让排序键和索引列顺序完全一致执行计划里就能看到INDEX FULL SCAN而不是SORT ORDER BY。这里有个经验分页SQL的排序字段最好和某个索引的前导列匹配否则Oracle还要额外做一次排序大表下代价很高。另外查询条件里的过滤字段如果参与索引也能大幅减少排序的数据量。比如按部门过滤后再分页在(deptno, hiredate)上建联合索引数据库先定位部门再按hiredate顺序取数据排序开销几乎为零。设计索引时要把WHERE查条件和ORDER BY排序字段一起考虑而不是只给单个WHERE字段建索引。6. 常见问题与避坑实录6.1 问题速查表把日常工作中最常见的几个分页问题整理一下基本覆盖了90%的咨询场景问题现象根本原因解决办法WHERE ROWNUM 20查不出数据ROWNUM边分配边丢弃序号始终从1开始用三层嵌套或ROW_NUMBER()、OFFSET FETCH相邻两页数据重复或缺失ORDER BY字段不唯一执行顺序不稳定排序键加主键或不重复字段11g上写OFFSET FETCH报ORA-00933该语法12c才引入换ROWNUM三层嵌套排序字段有NULL结果顺序不对默认NULL排序规则各场景不一致显式加NULLS FIRST/LAST分页越翻越慢OFFSET深需要扫描大量数据延迟关联、键集分页、加强索引一对多join后分页条数不对主表一行被明细行撑成多行先对主表编号或先去重查询带大字段分页内存耗尽SELECT *带CLOB等大字段参与排序延迟关联先拿ID再回表分页和总数统计性能双低两条SQL分别扫描小表用COUNT(*) OVER()合并大表走缓存或异步统计6.2 我踩过的几个坑第一个坑是排序字段不唯一导致的分页错乱。做考勤报表时按员工入职日期排序分页结果领导翻页后说数据不对同一页里出现了重复员工。排查半天发现同一天入职的有几十人Oracle没给稳定顺序。后来所有分页SQL强制ORDER BY主键加上第二排序键这个问题彻底消失。第二个坑是11g环境用了OFFSET FETCH。当时新项目初始库是19c开发环境测没问题上线前切到客户的11g库里接口直接报ORA-00933当时还以为是SQL拼接问题查了半小时才发现是数据库版本不支持。从那以后我每个项目启动都会确认数据库版本并且把分页SQL生成器做成按版本自动切换。第三个坑更隐蔽是ROWNUM写法配合ORDER BY的优化器行为。我写过一条SQL内层ORDER BY用了函数表达式比如ORDER BY TO_CHAR(hiredate, yyyy-mm-dd)导致索引完全失效每次分页都对全表做排序和函数计算。改成直接排序原始日期字段或者建函数索引后性能才有明显改善。核心教训是排序字段尽量用裸列不要在ORDER BY里包函数。还有一个经验动态拼接分页SQL时把ORDER BY子句用字符串拼进去之前一定要对排序列做白名单校验防止注入。我见过有人直接把前端传的排序字段拼进SQL结果被拖库的案例。排序字段白名单校验成本极低但安全收益很高强烈建议在ORM层就做好。结尾我在实际项目里的习惯是这样的如果数据库是11g无脑用ROWNUM三层嵌套存储过程和动态SQL全是这套模板数据库是12c以上新写的分页SQL优先OFFSET FETCH代码可读性好交接成本低ROW_NUMBER()更多用在统计报表或者主从表去重分页这类特殊场景不会拿它做百万级大表的深分页。遇到真正的大数据量分页直接上键集分页或者延迟关联多花一点代码量换来的稳定性完全值得。最后再说一个小技巧分页SQL写完顺手EXPLAIN PLAN看两眼执行计划确认有没有意外的全表扫描和排序这个习惯能帮你提前避开后面95%的线上问题。