Oracle亿级无主键大表快速批量遍历全表的实现方案

• 语法错误：Oracle 不支持直接用(列1,列2) > (值1,值2)这种行比较范围筛选写法，同时你传入的1、2是数值类型，和CLIENT_ID、ACCOUNT_ID的VARCHAR2类型不匹配，会触发隐式转换导致索引失效。
• 逻辑错误：你将ROWNUM过滤写在ORDER BY之前，Oracle会先随机取1000条数据再排序，无法拿到全表按索引排序后的连续批次数据。

你已经有CLIENT_ID+ACCOUNT_ID的联合唯一B树索引，用键集分页是亿级表遍历的最优方案，全程走索引范围扫描，每批查询性能稳定不会随遍历进度衰减，比偏移量分页性能高几个数量级。

全版本Oracle兼容写法 #

SELECT * FROM (
    SELECT CLIENT_ID, ACCOUNT_ID, -- 按需取字段，不要直接写SELECT*
        你需要的其他字段
    FROM CLIENT_ACCOUNT
    ORDER BY CLIENT_ID, ACCOUNT_ID
)
WHERE 
    -- 首次查询时去掉这段条件，直接取第一批1000条
    (CLIENT_ID > :last_client_id) 
    OR (CLIENT_ID = :last_client_id AND ACCOUNT_ID > :last_account_id)
AND ROWNUM <= 1000;

入参说明：:last_client_id、:last_account_id是上一批查询返回的最后一条记录的对应字段值，逐批迭代直到返回结果为空即遍历完成。

Oracle 12c及以上简化写法 #

性能和上面写法完全一致，语法更简洁：

SELECT CLIENT_ID, ACCOUNT_ID, 你需要的其他字段
FROM CLIENT_ACCOUNT
WHERE 
    (CLIENT_ID > :last_client_id) 
    OR (CLIENT_ID = :last_client_id AND ACCOUNT_ID > :last_account_id)
ORDER BY CLIENT_ID, ACCOUNT_ID
FETCH NEXT 1000 ROWS ONLY;

• 批次大小建议设置在1000~5000之间，太小会增加数据库交互次数，太大会导致单次IO过高、Undo占用过大。
• 如果业务需要的字段不多，建议把需要的字段加到UK_ACCOUNT索引中做成覆盖索引，完全避免回表操作，性能可以提升2~3倍。
• 不要用OFFSET分页，OFFSET越大需要扫描的无效数据越多，亿级表遍历到后半段会完全跑不动，只有键集分页能保证每批查询耗时稳定。
• 资源充足的情况下可以开启4~8的并行度，进一步提升遍历速度，注意不要占用过多数据库资源影响正常业务。

内容的提问来源于stack exchange，提问作者ic2y