在数据库开发里，“Oracle：分批查询”这六个字看着简单，但真正落到生产环境，背后牵扯的东西其实不少。做报表、跑批任务、数据迁移、接口对接，只要数据量上来了，一次性把几百万行全查出来再处理，内存扛不住、回滚段暴涨、全表扫描把IO打满，隔壁业务都要跟着遭殃。分批查询的核心目的就一句话：把大任务切碎成小任务，让每一次数据库操作都在可承受的范围内完成，而不是让一次查询把所有资源都吃光。
这篇内容适合几类人看：刚接触Oracle、写SQL经常一查就是全表的新手，正在做数据清洗或迁移、需要对大表反复抽取数据的开发，还有维护老系统、经常跟PL/SQL批处理打交道的运维同学。我会从底层原理讲到具体写法，再到参数怎么调、踩过的坑怎么避，尽量把这条路上该有的细节都铺开。
1. 为什么非要做分批查询
先想明白一个问题：为什么不能一条SQL把数据全捞出来？
最直接的原因在于内存和资源的物理限制。Oracle执行查询时，如果客户端把结果集一次性拉回，服务端需要为这个操作分配排序区、临时表空间，客户端也要在内存里装下全部数据。一张千万级的大表，全量查询时排序区不够就会溢出到临时表空间，临时表空间又不够就会报ORA-01652之类的错误。就算侥幸查出来了，应用程序端用List或者数组接住全部数据，JVM或应用进程的内存压力也会瞬间拉高，GC频繁、接口超时，这些都是生产环境真实发生过的故障。
第二个原因是锁和一致性的问题。大批量查询如果走全表扫描，会在表级加上共享锁，期间如果有其他会话在做DML操作，要么被阻塞，要么产生大量UNDO记录。尤其在一些7x24小时运行的系统里，夜里跑批任务和业务高峰重叠，长事务长时间占用资源，很容易把整个实例的吞吐量拖下来。分批查询天然会把操作切割成多个小段，每段占用资源的时间变短，锁的持有窗口也变窄，对并发的影响会小很多。
第三个原因很多人都忽略：分批查询本身是容错的需要。一次性处理一万条，如果第5000条数据格式有问题导致程序报错，你很难定位到底是谁出了问题。但如果每批1000条，处理完一批记一批日志，哪批报错就处理哪批，排查范围一下子缩小很多。数据像流水线一样一拨一拨往前走，哪里卡住修哪里，这才是工程上更可控的做法。
所以分批查询不是“不得已才用的技巧”，而是大数量处理场景下的基本素养。它带来的好处非常明确：降低资源峰值、缩短锁持有时间、提升故障定位效率。后面讲的每一种实现方式，都是围绕这几个目标展开的。
2. SQL层面的分批查询实现
2.1 经典ROWNUM分页带来的坑
老一代Oracle开发者对这段SQL都不陌生：
SELECT * FROM ( SELECT t.*, ROWNUM rn FROM your_table t WHERE ROWNUM <= 20000 ) WHERE rn > 10000;逻辑上很清晰：先取前20000行，再在外层过滤掉前10000行，中间那10000行就是目标分页。但真正在生产环境跑过的人都知道，这个写法有隐患。ROWNUM是在结果集产生时逐行分配的，它只能小于某个值，不能直接大于某个值，所以内层必须嵌套一层子查询，把ROWNUM先赋出来，外层才能做范围的筛选。
这里最容易踩的坑是排序。如果内层没有ORDER BY，每次执行分页拿到的数据顺序可能不一样，结果就是不同页之间出现重复数据或者漏数据。比如用户翻到第3页的时候翻到了第2页已经看过的内容，这种体验在生产报表里就是事故。要解决排序问题，必须在子查询里先排序再赋ROWNUM：
SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM your_table ORDER BY create_time ) t WHERE ROWNUM <= 20000 ) WHERE rn > 10000;但这样写还有一个隐患：ORDER BY如果走的是全排序，每次翻页都要把整张表的所有数据排一遍，越往后翻性能越差。我曾经在一个千万级的流水表上测过，翻到第100页之后，单页查询时间从最初的几十毫秒涨到了好几秒，这就是典型的深分页问题。所以ROWNUM方式适合数据量不大（百万以内）或者只取前几页的场景，深分页不建议硬扛。
2.2 ROW_NUMBER窗口函数的标准写法
从Oracle 9i开始支持的分析函数，在处理分批查询上比ROWNUM要规范得多。最常用的写法是：
SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY create_time) AS rn FROM your_table t ) WHERE rn BETWEEN 10001 AND 20000;这种写法在处理“每个分组内的分批查询”时有天然优势。比如要按部门分批导出员工数据，每个部门取前100条，PARTITION BY就能精确控制分组的数量。相比ROWNUM，ROW_NUMBER()可以让我们直接在同一个结果集里定位任意区间，不需要嵌套那么多层，逻辑也更清晰。
但需要注意，ROW_NUMBER()会把排序结果全部算出来再裁剪，如果表很大，分析函数的排序开销依然存在。这里不需要过度迷信窗口函数，它最大的价值在于逻辑表达能力强，适合复杂的分组分批场景，性能上并没有比ROWNUM质变。
2.3 12c+的OFFSET FETCH语法
如果数据库版本是12c及以上，Oracle引入了接近MySQL和PG风格的FETCH子句：
SELECT * FROM your_table ORDER BY create_time OFFSET 10000 ROWS FETCH NEXT 10000 ROWS ONLY;这是可读性最好的写法，Oracle官方也一直在推动它替代ROWNUM分页。但生产环境选型时要多注意一点：老系统里依然大量存在11g实例，代码写到12c语法上去，在低版本上直接报错。所以工程上要看基线版本，如果维护的是一个11g为主的环境，推荐还是用ROWNUM或分析函数，别为了简洁引出一个兼容性问题。语法越新越漂亮是一回事，能跑起来才是另一回事。
3. PL/SQL中的批量处理思维
SQL层面的分批查询，更多是“查询结果的分段获取”。而PL/SQL中则完全不同——它讲究的是“批量收集、批量绑定、循环提交”，这三板斧才是真正意义上批量处理的核心。
3.1 BULK COLLECT的LIMIT控制
很多新手把PL/SQL当成逐行处理的工具，循环里一条一条SELECT INTO，一条一条UPDATE，这种写法在数据量小的时候没什么，一旦上了量级，上下文切换的开销会大到吓人。
PL/SQL引擎和SQL引擎之间的交互是有代价的，每切换一次，都要重新解析、传递参数、拿结果，这个过程非常耗时。用游标循环逐行处理100万条数据，就是在SQL引擎和PL/SQL引擎之间来回切换100万次。而BULK COLLECT可以把一大批数据一次性从SQL引擎拉进PL/SQL的集合里，切换次数从100万次降到几十次，性能差距是数量级的。
DECLARE CURSOR cur IS SELECT id, name FROM big_table WHERE status = 'P'; TYPE t_id_list IS TABLE OF big_table.id%TYPE; TYPE t_name_list IS TABLE OF big_table.name%TYPE; v_ids t_id_list; v_names t_name_list; BEGIN OPEN cur; LOOP FETCH cur BULK COLLECT INTO v_ids, v_names LIMIT 1000; EXIT WHEN v_ids.COUNT = 0; -- 在这里对v_ids和v_names做业务处理 FOR i IN 1..v_ids.COUNT LOOP dbms_output.put_line(v_names(i)); END LOOP; END LOOP; CLOSE cur; END; /LIMIT 1000就是控制每批拉多少行进内存。这里有个容易忽略的点：FETCH BULK COLLECT ... LIMIT n在集合为空时COUNT为0，循环退出条件一定是EXIT WHEN v_ids.COUNT = 0，而不是判断游标%NOTFOUND。因为在LIMIT模式下，游标可能还没取完，但当前批次已经没数据了，判断条件写错了会漏掉最后一批数据。
3.2 FORALL替代逐行DML
拿到了批次数据之后，如果要写回数据库，用FORALL替代逐行循环是推荐的。
DECLARE CURSOR cur IS SELECT id, new_status FROM big_table WHERE process_flag = 'N'; TYPE t_id_list IS TABLE OF big_table.id%TYPE; TYPE t_status_list IS TABLE OF big_table.new_status%TYPE; v_ids t_id_list; v_status t_status_list; BEGIN OPEN cur; LOOP FETCH cur BULK COLLECT INTO v_ids, v_status LIMIT 1000; FORALL i IN 1..v_ids.COUNT UPDATE big_table SET process_flag = v_status(i) WHERE id = v_ids(i); COMMIT; EXIT WHEN v_ids.COUNT = 0; END LOOP; CLOSE cur; END; /FORALL的作用是把整个集合一次性发送给SQL引擎，而不是每个元素单独发送。它能做到这一点，是因为Oracle会对同一个DML语句复用执行计划，只在绑定变量上做批量的值替换。这里需要注意COMMIT的位置——把COMMIT放在每批循环之后，可以避免一个大事务长时间持有锁，也方便失败时从上一批的断点续跑。每批1000条提交一次，100万条数据就是1000次提交，这个频率对回滚段和日志的压力都很友好。
3.3 为什么LIMIT不要设太大
有一个很常见的误区：觉得LIMIT设得越大性能越好。其实不是这样。
每批拉1万条进内存，集合变量占用的PGA空间会变大。如果同时开多个会话并行跑类似的批处理，PGA总和超过实例配置的pga_aggregate_target，Oracle就会开始做临时表空间溢出，性能反而急剧下降。更麻烦的是，批次越大，失败重来的成本越高。想象一下你处理到第80万条时数据格式报错，如果每批1万条，前面80批全白跑了，但如果每批1000条，最多只丢当前这一批。
根据我的经验，一般推荐把LIMIT设置在500到5000之间，具体的取值取决于单行数据宽度和数据总量。字段少、单行几十字节，5000没问题；字段多、包含大对象或者长字符串，300到500分本。没有绝对正确的大小，要结合真实环境测。
4. 批次大小怎么选才合理
4.1 从执行计划反推批次量的思路
有一种方法能帮你科学地确定批次大小：观察执行计划中的Rows预估和Bytes值。
EXPLAIN PLAN FOR SELECT * FROM big_table WHERE status = 'P' AND create_time >= DATE '2024-01-01'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);执行计划里如果显示Bytes大约几十兆，说明这批数据在内存里能放得下，LIMIT取1000是安全的。如果单批几百万字节，LIMIT还设5000，内存压力就大了。于是你可以根据行宽和总行数反推一个安全批次数。这个方法虽然不能精确计算，但能让你对数据量有一个量化的感知，比拍脑袋设LIMIT要靠谱得多。
4.2 游标与集合的内存代价
PL/SQL里使用的嵌套表、关联数组、VARRAY，默认都存在PGA中。很多开发者不关心PGA，但批处理恰恰是PGA消耗大户。一个1万元素的嵌套表，每个元素里还有几个VARCHAR2(200)字段，轻松就是几兆内存，看起来不多，但10个并发会话一起跑，PGA就可能被吃满。
所以排查批量处理的性能问题时，我会习惯性地查一下当前PGA的使用情况：
SELECT name, value FROM v$pgastat WHERE name IN ('total PGA allocated', 'maximum PGA allocated');如果发现PGA增长明显，优先调小LIMIT，而不是去调大pga_aggregate_target。调大实例参数看起来是“加内存”，实际是在掩盖程序写得粗糙的问题，而且可能引发其他会话的内存竞争。
4.3 事务粒度与回滚段的关系
批处理里COMMIT的频次直接决定UNDO表空间的消耗。每批提交一次，回滚段里保留的旧版本数据只需要覆盖当前这一批，UNDO的压力就很小。如果一次性处理百万行不提交，UNDO里要保存这百万行修改前的镜像，表大一点，UNDO表空间随时可能爆掉。
生产环境里我遇到过一次ORA-30036: unable to extend segment by ... in undo tablespace的故障，排查到最后就是一段批量更新脚本没有分批提交，几百万条UPDATE攒在一个事务里。后来把脚本改成每500条一批提交，UNDO占用立刻降了下来，再也没报过这个错。这个经验说明：批次大小不只是性能问题，还是稳定性问题。
5. 分批查询常见问题与排查实录
5.1 ORA-01795: 最大表达式个数限制
这是分批查询场景下最高频的报错之一。当你在SQL里写WHERE id IN (1,2,3,...)且括号里超过了1000个值时，Oracle就会抛出这个错误。
分批查询经常需要处理这种情况，比如从第一批拿了1000个ID，下一批业务要用这些ID去关联另一张表。1000个刚好是Oracle的硬限制，一旦业务逻辑里把两批拼在一起，2000个ID的IN列表直接报错。
解决方案有两个思路。第一个是保持批次SQL和业务处理SQL的边界一致——一批1000个ID就只查这1000个，不要跨批拼接。第二个是使用临时表：把分批查到的ID先INSERT到全局临时表，后续的查询通过JOIN临时表来实现，彻底绕开IN列表的长度限制。第二种方案在处理跨批次的结果集合并时特别有用，而且临时表的统计信息可以由动态采样保证，性能也说得过去。
5.2 分批更新的数据一致性陷阱
分批查询常常伴随后续的数据更新。这里最常见的坑是“查询结果与更新结果不一致”。
举个例子：你要把status='P'的数据按批次更新为status='D'，每批更新完再查下一批。如果游标在OPEN前已经固定了结果集（Oracle的读一致性以语句启动时间为准），那么后面批次查询时，前面已更新的数据可能还会出现在结果集里（因为游标启动时它们还是status='P'），导致重复处理。
正确做法是在业务逻辑里加一个“已处理标记”，或者让游标的查询条件天然避开已处理的数据，比如加一个AND process_flag = 'N'，每批更新时同时把process_flag置为'Y'。这样下一批查询自然过滤掉已处理的数据，即使读一致性快照还没刷新，也不会重复处理。
5.3 排序键选择不当导致的数据错乱
分批处理如果没有靠谱的排序约束，很容易出现两个问题：同一批数据被重复取出，或者某一批数据被漏掉。
我见过一个真实案例。某数据同步程序用ORDER BY create_time做分页，但create_time在同一毫秒内有大量重复值。第一批取前1000条、第二批取第1001到2000条时，由于Oracle不保证相同排序键的返回顺序一致，某些行会被重复返回，另外一些行则被漏掉，最终同步到目标库的数据出现大量重复和缺失。
解决方法很简单：排序时加入一个具备唯一性的字段作为第二排序键。
ORDER BY create_time, id这样即使前一个排序键重复，后一个排序键也能保证全局顺序唯一，分批数据就不会错乱了。
5.4 ORA-00918: 列名不明确
用SELECT t.*配合分析函数或子查询时，如果外层SELECT引用了重名字段，很容易报ORA-00918: column ambiguously defined。
比如表里有id，子查询里生成的rn和外层字段重名，或者多个表JOIN后*展开出两个id，外层查询再WHERE id=就懵了。排查思路就是给每个字段加别名前缀，别偷懒用*。分批查询的SQL往往层数多，字段别名写清楚，自己调试时也轻松很多。
5.5 深分页性能为什么越来越慢
ROWNUM方式分页越往后越慢，是很多系统的通病。原因前面提到过，每次要“先取前N行再过滤前M行”，N越大，里面那层查得越久。生产环境深分页的优化方向有两个：
一是把分页逻辑改造成基于游标的“流式分批”，客户端依靠FETCH BULK COLLECT ... LIMIT一批批往前取，不依赖页数。二是把深分页查询改成基于游标位置的方式，比如记录上一批最后一条数据的ID，下一批用WHERE id > 上一批的id AND ...来取，这样每次查询的扫描范围都是局部增量，不会越跑越慢。
第二种方式的SQL长这样：
SELECT * FROM big_table WHERE status = 'P' AND id > :last_id ORDER BY id FETCH FIRST 1000 ROWS ONLY;每次拿到1000条，记录当前这批最大ID作为下一批的:last_id。这个方案的代价是必须有一个全局有序的游标键，但换来的性能提升非常明显，在千万级表上的分页遍历，速度能比ROWNUM深分页快一个数量级。
6. 一个真实的分批数据迁移案例
空谈理论没意思，我把之前做过的一个数据迁移项目完整梳理一下，场景很简单：某业务系统的流水表，存量数据1200万条，需要按条件抽取到新表，同时做字段格式调整。
项目约束比较苛刻：只能在一台数据库服务器上操作，不能新增硬件资源；迁移必须在业务低峰期4小时内完成；原表不能长时间锁住，其他应用还要继续读写。
6.1 核心方案的取舍过程
我最初想用一条INSERT ... SELECT直接完成全量迁移，但很快否定了。1200万条的数据量，单条INSERT SELECT会占用大量UNDO，而且原表会被锁定很长时间，业务方不能接受。
于是改成PL/SQL批量迁移，分批大小定在2000，每批做完提交一次。选择2000而不是更大的原因，是考虑到目标表还建了几个索引，单批条数太大会让索引维护的负担集中在一瞬间。2000条一批，既能把PGA占用控制在合理范围，又不会频繁提交到影响性能。
迁移过程中顺手做了两件事：一是关闭了目标表的日志追加模式，二是把目标表的几个非关键索引先DROP，迁移完成后再重建。前一个操作减少了日志生成量，后一个操作避免了每条INSERT都要维护多个索引的开销，这两个改动让整体迁移速度提升了至少一倍。
6.2 迁移脚本的核心结构
DECLARE CURSOR src_cur IS SELECT id, biz_date, old_field, created_time FROM old_biz_table WHERE biz_date >= DATE '2023-01-01' ORDER BY id; TYPE t_src_rec IS RECORD ( id old_biz_table.id%TYPE, biz_date old_biz_table.biz_date%TYPE, old_field old_biz_table.old_field%TYPE, created_time old_biz_table.created_time%TYPE ); TYPE t_src_tab IS TABLE OF t_src_rec; v_src_data t_src_tab; v_count NUMBER := 0; BEGIN OPEN src_cur; LOOP FETCH src_cur BULK COLLECT INTO v_src_data LIMIT 2000; EXIT WHEN v_src_data.COUNT = 0; FORALL i IN 1..v_src_data.COUNT INSERT INTO new_biz_table (id, biz_date, field_x, created_time) VALUES (v_src_data(i).id, v_src_data(i).biz_date, CASE WHEN v_src_data(i).old_field = 'A' THEN 'X' WHEN v_src_data(i).old_field = 'B' THEN 'Y' ELSE 'Z' END, v_src_data(i).created_time); v_count := v_count + v_src_data.COUNT; IF MOD(v_count / 2000, 20) = 0 THEN DBMS_OUTPUT.PUT_LINE('已处理: ' || v_count || ' 行'); END IF; COMMIT; END LOOP; CLOSE src_cur; DBMS_OUTPUT.PUT_LINE('迁移完成，总行数: ' || v_count); END; /这段脚本有两个细节值得说。第一个是ORDER BY id的用途：确保迁移进度具有单调性，万一脚本中途报错停止，下次可以从记录到的最大ID继续跑，不用从头再来。第二个是IF MOD(v_count / 2000, 20) = 0这个日志打印位置的写法——不要每批都打印，每20批打印一次，既能看到进度，又不会因为输出过多拖慢性能。
6.3 遇到的一个有意思的问题
第一次跑迁移脚本时，执行到大约三分之一处突然报ORA-04031，共享池内存不足。刚开始以为是批次设太大，把LIMIT从2000调低到800，重新跑还是报同样的错。
后来查了v$sgastat，发现问题不在PL/SQL，而在FORALL内部频繁的SQL解析。老环境的共享池本身就不大，迁移脚本每批都要硬解析一次INSERT语句，加上其他业务系统同时也在跑，共享池被挤爆了。
解决办法是给这段迁移脚本做了一个DBMS_SHARED_POOL.KEEP标记，把核心SQL固定到共享池里。其实更实用的做法是把INSERT语句做成存储过程或者直接用绑定变量，减少解析负担。经过这个调整之后再跑，问题消失，整体速度也稳定在预期范围内。
6.4 迁移结束后的验证清单
数据迁移完成后，不能只盯着“没有报错”就收工。我在这个项目里建立了一套验证流程，现在每次做类似工作都会照做一遍。
第一项是数据量比对，分别统计源表和目标表的总行数，确保两条SQL跑出来的数字完全一致。第二项是抽样校验，写几个按业务日期和业务类型分组的聚合查询，对比源表和目标表的结果。第三项是主键重复检查，重点查目标表有没有因为批次之间数据重叠产生重复主键。第四项是边界值检查，把业务日期第一天、最后一天的数据单独拿出来核对，防止游标边界处理不当导致头尾数据丢失。
这套验证流程不能说100%覆盖所有隐患，但对最常见的分批处理事故——重复、遗漏、错乱——都有对应的检查手段。多花半小时做验证，能省掉上线后半夜被人叫起来排查的折腾，这笔账怎么算都划算。
7. 分批查询的扩展场景与应用思路
分批查询不只是数据迁移会用，很多日常开发场景都离不开。我这里列举几个实际碰到的场景，算是帮大家把思路打开。
第一个场景是接口批量同步。某外部系统不支持传入超过500个ID的批量查询，我们的做法是在本地分批查Oracle的数据，每批500个ID组织一次请求，循环调用外部接口。这里的核心问题不是Oracle这边，而是外部接口对批次大小的限制，所以LIMIT要跟着外部系统的约束走，而不是自己想设多少设多少。
第二个场景是报表定时生成。每天凌晨要生成一份包含上百万行明细的报表文件，一次性全量查出来再写文件，内存很容易被打爆。我的做法是用PL/SQL分批把数据写入目标表，或者直接用SQL写文件的方式配合分批游标，每批写一批内容到文件，写完释放一批。这样生成过程中即使某批失败，重新从失败批次继续即可，不用从头再跑。
第三个场景是数据归档。生产库只保留最近三个月的数据，三个月前的要迁移到历史库。这里分批查询的关键点在于归档和数据删除的原子性——一批数据先插入历史库，确认成功后再在源库删除同一批数据，两个操作中间要严格控制批次边界，用ID区间切分是最稳妥的做法，不能依赖模糊的时间条件做切分。
这些场景共同验证了一件事：分批查询不是孤立的写法技巧，而是一种系统性的处理思路。它的核心永远是约束资源使用、控制事务粒度、精确记录进度，只要围绕这三个原则去设计，无论底层用的是ROWNUM、分析函数、还是BULK COLLECT，方向都不会错。
8. 最后想分享的一点实操体验
分批查询写起来不难，真正难的是在生产环境里把“分批”这件事做到无感。我这些年踩过不少坑，掉坑最多的不是写法本身，而是对环境和数据的估计不足。
个人经验是：拿到任何一张需要分批处理的表，先不要急着写循环，先做三件事。第一件事是查这张表的数据量级和分布情况，用COUNT(*)配合按时间或按ID区间的分布查询，知道数据集中在哪些区间。第二件事是看一眼执行计划，确认查询条件能走索引，避免每批都触发全表扫描。第三件事是观察一下目标环境的PGA和临时表空间剩余情况，心里有数再决定批次大小。
另外，所有跑批任务一定要有断点续跑能力。让脚本支持从指定位置继续处理，不是加分项，而是必选项。设计数据结构的时候就要预埋处理标记字段，比如process_flag，每处理完一批就更新标记，这样即使中途报错，重新启动脚本时也能快速定位到断点，不用从头再来。
最后分享一个最容易被忽视的小细节：分批脚本的输出日志一定会写上批次数和当前处理区间，别只记录“成功/失败”两个状态。数据量一大，没有区间信息的失败记录形同虚设，你根本不知道从哪一批开始排查问题。多打一行日志，关键时刻能救你一整晚的睡眠。
