Oracle大批量数据更新总体思路：避坑指南与关键原则

Oracle大批量数据更新的方案预设前言一、执行范围二、索引三、旧数据/脏数据清理四、更新时长估算五、更新方式1.分批次更新2.分批次事务提交3.分页查询4.暂停更新功能六、更新失败处理1.日志记录2.事务回滚方式七、风险排除1.表空间风险2.索引风险3.内存风险4.日期风险八、数据备份九、数据更新时机十、数据恢复十一、数据验证十二、庆祝一下前言本文旨在提供完善的总体思路避免遗漏或出现严重问题具体Oracle命令太过繁琐不在此陈列。一、执行范围首先我们要确定更新哪些表每张表有多少待更新的数据量每张表更新哪个字段每个字段的类型是什么字段最大长度字段是否有索引如下所示表名数据量字段(类型)最大长度T_ORDER120WORDER_XML(CLOB)180wT_MESSAGE240WMSG_CONTENT(CLOB)59w二、索引由于表数据量比较大select和update语句的where条件要使用主键或者有索引的列有效避免全表扫描减少锁表时间。三、旧数据/脏数据清理先检查一下表里和业务代码是否可以清理旧数据/脏数据如果可以说不定本来百万级的表可以瘦身到一两万的数据量大大减轻批量更新的压力。再检查一下脏数据会不会对你的更新逻辑产生影响。四、更新时长估算可以在本地或测试环境造模拟数据模拟生产环境真实的更新数据量以及每条更新的数据长度大小估算出一个语句更新时长评估时长是否可以接受若全部数据更新时间太长不能接受可以考虑是否可以先根据时间更新近期数据或本次只更新部分表数据更新完成后后续再持续更新。五、更新方式1.分批次更新如果单条数据占空间比较大那么单批次量就要小一些像我上面列举的CLOB类型的大字段每批次100~500条合理根据服务器配置决定如果单条空间小可以适当增加每批次大小。2.分批次事务提交不要一条一条提交事务也不要几百万才提交一次事务这里建议每次提交事务的量和上面批次的量一样既可。3.分页查询如果你是在Java中进行更新记得使用分页查询不要把几百万条数据一下查询到内存里最好手写分页框架的分页容易出bug。4.暂停更新功能比如要更新300w条数据可能会出现更新到150w条数据时发现之前更新失败的数据需要暂停当前更新处理好失败数据之后再继续更新需要做更新暂停功能这样就不用等待全部数据跑完才能处理异常数据了从程序设计而言是一个非常好的灵活性设计。六、更新失败处理1.日志记录要有完善的日志记录例如总数据量、每批更新数量、每批更新成功数量、每批更新失败数量、更新失败的数据ID、失败原因2.事务回滚方式要根据自身业务场景考虑好是一条失败不影响继续执行还是一条失败整批失败还是一条失败整表失败等等七、风险排除1.表空间风险如果你是更新clob字段并且现有表空间剩余不足的情况下就要谨慎一些因为clob字段在update的时候需要将新数据和旧数据同时存储碎片化但是并不是说比如旧数据有1G那就需要2GOracle有自动回收机制但是如果你的clob字段是BASICFILE就需要处理一部分就手动执行SHRINK SPACE命令来整理碎片释放空间。如果clob字段是SECUREFILEOracle的自动回收更积极但是仍有风险保险起见还是要对表空间进行扩容。2.索引风险如果你的update语句的where条件没有索引可能会导致update执行过慢这个过程是锁表的如果你的业务不依赖这张表那没事如果依赖可能会导致业务停滞。3.内存风险如果你是在java程序中去批量执行update语句要注意大字段在Java中的处理小心OOM内存溢出。4.日期风险如果你是通过日期去分批更新注意不同表的同一日期范围的数据量是不同的比如我日期范围是近一个月A表可能只有3000条数据B表会有50万条数据这种情况要考虑到。八、数据备份最好用expdb数据泵方式导出如果不行就使用exp命令如果exp命令也使用不了就使用下面的sql库内备份但是库内备份要注意备份完之后的表空间容量是否不足。-- 要注意这条语句是不同步备份索引、触发器、默认值、注释等等的只能备份表数据CREATETABLET_ORDER_20260401_BAKASSELECT*FROMT_ORDER;九、数据更新时机大批量的数据更新需要避开业务高峰期在系统使用率低数据库流量小的时候进行。十、数据恢复要提前写好数据恢复的脚本/命令不能等出了问题想恢复的时候现写尽量减少数据变化产生的差异下面列一条库内备份表的恢复命令比普通update快非常多。MERGEINTOT_ORDER bUSINGT_ORDER_BAK aON(b.ida.id)WHENMATCHEDTHENUPDATESETb.待恢复字段a.待恢复字段;十一、数据验证1、数据验证的时机要包括执行前验证、执行中验证、执行后验证2、需要提前写好数据验证的SQL最好自动化高一点不要一条一条执行然后在肉眼比对数据的那种SQL等更新执行完成之后达到一键验证的效果3、数据验证的SQL尽量考虑全面的一些针对不同的业务场景进行验证。十二、庆祝一下如果你按照本文的方案完美完成了重要数据更新那可以长舒一口气然后夸夸自己了