Oracle分批查询实践：从ROWNUM到keyset分页与批量DML优化

做 Oracle 开发或者 DBA 的迟早会遇到一个场景一张上千万行的核心表业务说要按时间导出全部明细或者要一次性把历史数据按状态批量刷掉。我早年接手一个报表接口某次运营导出近三个月订单接口直接卡死数据库会话里临时表空间暴涨最后只能把那个会话直接 kill 掉。问题根本不在于数据库性能差而是我们把自己变成了“一次拉全量”的思维模式。分批查询说穿了就是把一个大需求拆成多个小查询逐批处理它在 Oracle 里的价值远超“分页显示”这个字面意思。报表分页、批量 DML、跨库取数、数据归档几乎每个场景都绕不开它。这篇文章我会把 Oracle 分批查询的几种主流写法、内在原理、性能陷阱和实战循环都讲透适合正在做数据修复的开发也适合被深分页查询折磨过的 DBA。1. 为什么需要分批查询大查询的隐形代价1.1 哪些场景必须考虑分批先别急着聊 SQL写法得先搞清楚什么情况下非分不可。我自己的判断标准很简单凡是“一次把结果集完整塞进内存再处理”的做法在数据量上了千万级之后都值得重新考虑。常见的触发场景大概有四类。第一类是面向用户的真分页比如门户后台的列表、APP 的消息记录用户只是要看第 5 页那你就不该把前 5000 行全部读出来。第二类是大结果集导出报表、数据抽取、归档任务逻辑上确实要拿全部数据但物理上全部结果在 PGA 里堆积会让内存和临时空间同时吃紧。第三类是批量 DML比如清理几个月之前的失败单据涉及的行数可能是几十万甚至几百万一条 DELETE 下去就容易出 ORA-01555 或者锁冲突。第四类是 dblink 跨库取数远程库大表数据经网络搬回来不控制批次整个链路的瓶颈都会暴露出来。这四类场景有一个共同点实际需要的数据总量没有变但一次性的操作单元太大了。分批操作的本质就是通过缩小单次工作的粒度把不可控的偶发问题变成可控的、能推进的稳定步骤。1.2 一次性查询的四大隐患一次性查询最直观的问题是慢但慢只是表象。我处理过足够多这类故障之后会习惯把它拆成四个隐患来看。第一个隐患是 PGA 和内存压力。Oracle 的排序、哈希连接这些操作会优先使用 PGA 内存当结果集大到超过可用内存时优化器会把中间结果写到临时表空间也就是产生 temp 段 I/O。一次拉全量的大排序语句能让你的 temp 表空间眼睁睁看着涨上去。第二个隐患是 undo 表空间被长时间占用。一条查询只要还在执行它看到的“读一致性视图”就需要保留对应的 undo 数据。如果这个查询扫了几十分钟而数据库里还有人同时在改表那么旧的 undo 信息可能被覆盖查询就会报出 ORA-01555 snapshot too old。我见过最冤的一次是一个三十秒的查询刚好撞上了批量任务每小时一次的集中更新直接报了快照过旧。第三个隐患是行锁和阻塞。批量 UPDATE、DELETE 一次操作几十万行会在大量行上加锁事务执行期间这些锁都不释放。只要另有一个业务在操作同表马上出现等待极端情况下就是 ORA-3009 锁等待超时。第四个隐患是网络传输。就算临时表空间够大、undo 也够长整批数据从数据库传到应用服务器的网络开销依然可观。一次拉 100 万行和分 20 次拉 5 万行传输的字节数差不多但单次占用连接的持续时间和应用内存的峰值绝对不一样。1.3 不要忽略 Oracle 的游标与提数模型分批查询为什么能缓解这些问题得从 Oracle 的游标机制说起。一般的查询语句Oracle 会经历语法解析、生成执行计划、打开游标、逐行提数、关闭游标这几个阶段。应用端如果使用 JDBC 的默认方式通常是一次性把结果全部取回内存而我们说的“分批”本质上是控制了每次打开游标后提取的行数上限。PL/SQL 里的 BULK COLLECT 配合 LIMIT一次 FETCH 只取指定行数分页 SQL 里的 ROWNUM N 或 FETCH NEXT也是让优化器在拿到 N 行之后就适可而止。理解了这一层再看后面所有的写法都会顺畅很多你限制的不仅仅是传输量更是在限制排序中间结果、undo 保留时间、锁的停留窗口。2. 从 ROWNUM 到 FETCH FIRST分页写法演进与原理2.1 最经典的 ROWNUM 两层嵌套在 12c 之前Oracle 生态里最普遍的分页写法就是 ROWNUM。ROWNUM 是 Oracle 给结果集行赋的一个伪列从 1 开始递增。它最大的特点是产生顺序在 ORDER BY 之前。换句话说ROWNUM 是“把已经拿到的行先编号”不是“按指定顺序编号”。所以一个常见的错误写法是先加 ROWNUM 再排序导致分页顺序完全随机。正确做法分两层内层先做完整排序外层再限制 ROWNUM。如果要取第 21 到第 30 行示例长这样SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT empno, sal FROM emp ORDER BY sal DESC ) t WHERE ROWNUM 30 ) WHERE rn 20;看这个写法有几个坑。第一个是 ROWNUM 30 必须放在外层如果直接写 WHERE ROWNUM BETWEEN 21 AND 30结果永远为空因为第一行编号是 1条件不成立行被丢掉第二行又变成 1循环往复一行都出不来。第二个坑是如果没有最内层的 ORDER BY分页顺序没有意义。第三个坑是内层 ORDER BY 会触发一次完整排序它扫描的是全表数据不是只排 30 行。从 10g 开始优化器对这类 ROWNUM 嵌套有一定程度的自动改写Buffer Gets 不会像手写两层还要多扫一次。但在真正的海量数据下这个写法仍然会在每一页都重新执行一遍完整排序和扫描。2.2 ROW_NUMBER 分析函数写法Oracle 9i 引入的分析函数给分页提供了新思路。ROW_NUMBER() OVER (ORDER BY ...) 可以在排序结果上生成连续编号然后外层过滤行号区间即可SELECT * FROM ( SELECT empno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn FROM emp ) WHERE rn BETWEEN 21 AND 30;这个写法比 ROWNUM 嵌套清爽不少排序和编号逻辑都写在分析函数里一眼能读出来“先按 sal 降序再编行号再取区间”。执行计划里你会看到 WINDOW SORT 和 VIEW 算子优化器会基于 ORDER BY 产生排序动作。但注意它跟 ROWNUM 版本在深分页上一样要付出“完成整个排序”的代价。因为 ROW_NUMBER 必须知道所有行的排序位置才能给每一行编号至少在处理完排序列之后才能确定行号。取第 1000 页的数据和取第 1 页的数据在排序工作量上几乎没有差别区别只在最后丢掉多少行。2.3 12c 后的 OFFSET FETCH FIRST从 12c 开始Oracle 提供了标准的 SQL 分页语法跟其他数据库的 LIMIT/OFFSET 用法对齐SELECT empno, sal FROM emp ORDER BY sal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;OFFSET 20 表示跳过前 20 行FETCH NEXT 10 表示取接下来的 10 行。它可以简写成 FETCH FIRST 10 ROWS ONLY配合百分比的写法也支持。这个语法最大的价值是可读性和维护性执行计划其实会被优化器转换为基于 ROW_NUMBER 或底层窗口逻辑的内部表示本质上的代价跟 ROW_NUMBER 版本差不太多。也就是说它解决了写法上的痛苦没有解决深分页扫描量大的问题。2.4 三种写法的取舍与性能对比三种写法用表格感受一下差异写法生效版本可读性深分页代价适用场景ROWNUM 嵌套全版本一般高老库、11g 及以下ROW_NUMBER9i 起较好高复杂排序、分析场景OFFSET FETCH12c 起最好高新项目、标准分页我实际验证过同一张 800 万行表取尾部某一页数据的时候三种写法的执行计划殊途同归都走了 SORT ORDER BY逻辑读也很接近。真正拉开差距的不是“用哪种分页写法”而是“是否用到了可控条件下更高效的定位方式”。这就是下一节要展开的内容。3. 深分页优化基于键值的 keyset 分页3.1 深分页为什么越来越慢分页接口最常见的性能投诉是第一页秒开翻到后面越来越慢。原因我们在前面铺垫过OFFSET 越大需要排出来再丢弃的行就越多。取第 1 页排序后读 10 行即可取第 10000 页得先把前 100000 行全部排好再扔掉只留最后 10 行。用 ROAD 上的比喻这就像你去书店找第 500 页的书不是直接翻到那页而是从第 1 页开始一页一页翻。索引对 B-tree 结构是无序的快速定位但深分页的排序需求让索引很难直接起作用。3.2 基于主键阈值的 keyset 分页keyset 分页也叫“游标分页”或“键集分页”核心思路是上一页拿到最后一条记录的主键值下一页用这个值作为起点条件。比如按 empno 升序每页取 10 条-- 第一页 SELECT empno, sal FROM emp WHERE empno 0 ORDER BY empno FETCH FIRST 10 ROWS ONLY; -- 假设拿到最大的 empno 是 109下一页继续 SELECT empno, sal FROM emp WHERE empno 109 ORDER BY empno FETCH FIRST 10 ROWS ONLY;这个写法能利用主键索引做 INDEX RANGE SCAN直接从 empno109 这个位置开始向后扫。数据库不需要关心前面的 109 行长什么样它只从目标点出发找到 10 行就停。翻到第 1 万页代价跟翻到第 2 页几乎没有区别这是它最大的优势。代价是使用方式的改变客户端必须记住上一页的最后一个键值。也就是说你不能再靠一个 pageNum 参数随机跳转到任意页了它更适合“上一页 / 下一页”这种线性翻页或者报表导出、数据同步这种“从哪儿停从哪儿接着跑”的场景。3.3 多列排序下的 keyset 改造实际业务很少只按一个主键排。比如列表要按 create_time 倒序同时每个时间点可能有大量重复。这时单纯 WHERE id 上一页最大 id 就不对了因为排序键是 create_time。一个稳妥的改造方案是“排序列 主键”的三段式条件。假设记录按 create_time DESC 排序上一页最后一条的 create_time 是 t1id 是 id1那么下一页条件为SELECT * FROM t WHERE create_time :t1 OR (create_time :t1 AND id :id1) ORDER BY create_time DESC, id FETCH FIRST 50 ROWS ONLY;注意这里 create_time 是倒序所以等同字段的游标方向是小于id 是升序兜底所以是大于。三个字段拼成一个复合索引 (create_time DESC, id)效果最好。这种写法要理解一个细节当 create_time 严格小于 t1 时新页的第一行一定不会和上一页重复当 create_time 恰好等于 t1 时靠 id 大于 id1 来排除已经读过的行。这就是“把上一页的最后位置编码进查询条件”的完整形态。keyset 的坑在于排序方向一变不等号方向也要跟着变很多人在这里写反。写完之后务必用几行对比数据验一遍别只看第一页对就提交上线。4. 批量 DML 场景下的分批实践4.1 为什么不建议一条 DELETE 清理全表分页查询的价值当然不止于页面展示批量 DML 才是最考验 DBA 功底的地方。很多人写清理任务时习惯一条 DELETE 干掉所有符合条件的行DELETE FROM biz_log WHERE status DONE;数据量小的时候看不出问题。一旦到了百万行这条语句会持续很长时间期间持有大量行锁undo 不断膨胀redo 也在暴涨。别的会话想更新同一批数据全部堵在锁上谁要是同时执行了大查询还容易触发 ORA-01555。核心问题在于一条大 DELETE 是一个不可分割的事务。要么全部成功要么全部回滚中间没有任何阶段性成果。如果执行了二十分钟后业务发现条件写错了ROLLBACK 又要花更久。分批提交最大的好处不是提升单条速度而是把大事务拆成多个可以确认进度的小事务。4.2 BULK COLLECT 与 FORALL 的分批循环PL/SQL 里最标准的分批删除循环长这样DECLARE CURSOR cur IS SELECT rowid FROM biz_log WHERE status DONE AND rownum 1000; TYPE rid_tab IS TABLE OF UROWID; v_rids rid_tab; BEGIN LOOP BEGIN SELECT rowid BULK COLLECT INTO v_rids FROM ( SELECT rowid FROM biz_log WHERE status DONE ) WHERE ROWNUM 1000; END; EXIT WHEN v_rids.COUNT 0; FORALL i IN 1 .. v_rids.COUNT SAVE EXCEPTIONS DELETE FROM biz_log WHERE rowid v_rids(i); COMMIT; END LOOP; END;这个写法每轮用 ROWNUM 取 1000 个 rowid注意 ROWNUM 写在了外层它作用于子查询的结果集。取完一批FORALL 批量删除然后 COMMIT再进入下一轮重新发一条查询拿到的是提交之后的最新快照所以不会重复处理也不会漏处理。FORALL 比普通 FOR 循环快是因为它在 PL/SQL 和 SQL 引擎之间只做一次上下文切换把整批操作发给 SQL 引擎执行。加上 SAVE EXCEPTIONS即使其中某几行因为锁等待或约束失败报错也不会中断整批全部执行完之后错误信息可以从 SQL%BULK_EXCEPTIONS 集合里逐条读出来。批次大小我一般取 1000 到 2000。如果表里的行特别宽比如带 CLOB、上百个字段建议降到 300 到 500否则 PGA 里一次性打包这么多行的 rowid 虽然不大但 SQL 引擎在解析 FORALL 时的开销仍然存在。4.3 按主键区间分批的取舍BULK COLLECT ROWNUM 的方案很像“从头扫一遍分片删除”它不需要提前知道主键范围通用性强。但有时候业务的清理条件天然能用主键或者时间字段分段比如“按 id 每 5 万一个段”这种按主键区间的方案其实更稳DECLARE v_start NUMBER : 1; v_end NUMBER : 50000; v_max NUMBER; BEGIN SELECT MAX(id) INTO v_max FROM biz_log; WHILE v_start v_max LOOP DELETE FROM biz_log WHERE id BETWEEN v_start AND v_end AND status DONE; COMMIT; v_start : v_end 1; v_end : v_start 49999; END LOOP; END;这种方案的优势是每批的事务边界非常清晰DBA 一眼就能算出“当前删到哪个 ID 区间了”。如果中间出了错从断点继续跑也容易。劣势是如果 id 和清理条件没有关联比如你要按 status 删但 status 分布十分散乱BETWEEN 区间内的命中行极少就会出现大批量空转。所以按主键区间分批更适合“按时间归档”“按 ID 迁移”这类清理条件与主键顺序强相关的任务。4.4 dblink 与导出场景的分批取数跨库取数是另一个高发区。远程库的大表通过 dblink 直接 SELECT 全量通常会把远程数据库的临时表空间直接吃干抹净网络也扛不住。我习惯的做法是先在远程库一侧算好分片范围然后在本地循环取数。假设远程表 remote_tab 的主键是 id总数据量通过 MIN(id) 和 MAX(id) 确定平均切成 N 段DECLARE v_batch_size NUMBER : 50000; v_min_id NUMBER; v_max_id NUMBER; v_current NUMBER; BEGIN SELECT MIN(id), MAX(id) INTO v_min_id, v_max_id FROM remote_tabdb_link; v_current : v_min_id; WHILE v_current v_max_id LOOP INSERT INTO local_tab SELECT * FROM remote_tabdb_link WHERE id v_current AND id v_current v_batch_size; COMMIT; v_current : v_current v_batch_size; END LOOP; END;切成多少段取决于单批的数据行数和行宽。我一般让单批 INSERT 的行数控制在 1 万以内因为 dblink 的瓶颈往往在网络上批次太小网络往返太频繁批次太大又会出现远程库临时空间问题。还有一种做法是 12c 以后用 DBMS_PARALLEL_EXECUTE 做任务分片它可以按 rowid 或者主键自动分块适合更大规模的数据迁移但配置复杂度也高。中小型项目其实用简单的 ID 区间循环就够了关键是先把批次切明白再谈工具。5. 常见问题与排查实录5.1 ORA-01555 快照过旧分批循环里最常见的错误就是 ORA-01555。发生原因一句话查询需要的旧版本 undo 已经被覆盖了。大查询执行时间长读一致性快照期间其他事务不断更新同一批数据块undo 里保存的镜像被新事务覆盖老查询就找不到它需要的版本了。分批查询本身就能大幅缩短单条查询的持续时间是规避 ORA-01555 的最有效手段。但如果把循环里的 COMMIT 去掉反而会变成“一个大事务里的多批查询”照样长时间持有快照问题依旧。另外如果你在循环里一边查询一边由别的会话修改同表相关数据还是要把 undo 表空间调大一些或者和业务窗口错开。5.2 临时表空间暴涨分页排序、GROUP BY、聚合这些操作在 PGA 不够时会把中间结果写进 temp 表空间。我排查这类问题的基本路径是先用 v$sort_usage 找占用大的会话和 SQL_ID再通过 DBMS_XPLAN.DISPLAY 确认排序算子最后看是否可以用索引避免全量 SORT。如果你用 OFFSET 翻到很深的页执行计划里 SORT ORDER BY 的代价会非常大因为排序结果必须完整生成才能丢弃前面 N 行。这是结构性问题临时表空间给多大都只是暂时缓解真正的解法是换成 keyset 分页让每次操作只牵连一小段数据。5.3 ROWNUM 与排序的经典踩坑ROWNUM 分页的坑值得单列一条。有人图省事写成SELECT * FROM ( SELECT empno, sal, ROWNUM rn FROM emp WHERE ROWNUM 100 ) WHERE rn 90 ORDER BY sal DESC;这种写法取出来的数据顺序是乱的因为 ROWNUM 是在 ORDER BY 之前分配的。先分页后排序等于在随机顺序里取了一段再做排序结果就是每次翻页都可能看到重复记录或者漏记录。另外一个高频错误是 WHERE ROWNUM 10前面分析过这个条件永远返回空。记住一个原则ROWNUM 只能配合 使用且要放在内层对有序结果做限制取区间必须再包一层。5.4 绑定变量与分页参数分页 SQL 如果直接把页码拼成字符串每个页面对应不同 SQL 文本Oracle 每次都要硬解析严重的时候能把共享池塞满。我见过一个报表系统一晚上把 shared pool 撑爆最后定位就是分页查询的页码全部被当成字面量拼进 SQL。分页参数应该用绑定变量。keyset 分页更是离不开绑定变量因为每个用户翻页时上一页最后的 id 都不一样不绑定的后果就是执行计划被“第一个进来的人”带偏。绑定变量还能让游标共享减少硬解析的 CPU 开销。5.5 版本差异的适配最后说版本。11g 及以下没有 OFFSET FETCH 语法只能用 ROWNUM 或 ROW_NUMBER。12c 以后推荐用标准语法但要注意OFFSET 如果为 0FETCH FIRST 可以直接写如果带 OFFSET有些开发手滑写成 OFFSET 0 ROWS 还能编译通过但可读性差。如果需要兼容 11g 和 19c 两套环境最稳妥的底层逻辑是 ROW_NUMBER 方案因为它两个版本都支持执行计划的形态也清晰。ROWNUM 嵌套虽然也兼容但肉眼维护起来容易出上面说的坑团队接手成本也更高。按我这么多年的经验分批查询这件事真正难的不是某一种写法而是你是不是能从“一次拿完”的惯性里走出来。遇到几千行的表一次查完无可厚非遇到几十万上百万的数据先想清楚能不能切批怎么切事务边界在哪临时资源和锁会变成什么样几步想清楚了下手就有数得多。最后再分享一个小心得无论你在用哪种分页方案上线前最好把翻页测试做到第 30 页、第 100 页而不是只验证第 1 页。深分页的性能滑坡经常是渐进式的第一页看起来毫无问题翻到后面才露馅。多花十分钟做一次深页验证能帮你避免在最忙的时候被业务找上门。