批量更新9亿条数据，如何优化update？

img


A表

img


B表
两张表连接，当A.表中的sample_type=B.表中的TEST_ITEM_TYPE_NAME，将B表中的TEST_ITEM_TYPE_CODE赋值给A表中的TEST_ITEM_TYPE_CODE
直接写update 更新特别慢，还会导致表空间不足

img


想请教一下有没有什么好的方法，可以优化下这个update语句

首先,这个sql建议再加一个条件,你确定是每一行都要更新？
是否可能存在有部分数据已经满足你的结果了,但你这个更新语句并没有过滤掉那部分数据？或许需要更新的数据并没有那么多？
另外,你那个9亿的表是一直在动态变化还是存在有静态不用的时候？有没有考虑过直接create table as 两表关联来重新建一个表,再把表进行重命名及重建索引？
还有一种方式,关于批量更新,可以把要更新的数据写个游标,然后循环更新,设定批量commit 的行数,比如每更新1000行commit一次,然后这个还可以使用bulk collect

update会占用回滚段,尤其是你这9亿数据一把更新,如果还有索引的话会更慢。但create table as的方式会以数据块的形式直接复制数据。在oracle中如果涉及到大量数据要处理时,用create table as来替代insert /delete/update等操作是很常见的。
但一定要注意剩余表空间的大小、新表上要重新建索引,还有相关失效对象(比如引用了这个表的存储过程等)要重新编译。
反正是一次性执行的sql,当然是直接执行就好,你建完新表后还可以比较一下两边的记录数以及检查数据结果是否满足你要求了,再做后续处理。
但如果是我,我可能会选择循环去update,分批提交的方式,每批更新一点,让它慢慢跑,反正会更新完,而且不用怕中断,加上不等于的条件,已经更新过的就不用再更新了。
因为不确定你程序是什么情况,之后是否还有可能出现这个值要更新,不可能老是去把9亿条数据去重建新表

一般不建议关联表去update数据，因为这样一单出问题会误操作大量数据。
从DBA角度出发，应该进行拆分，先查出满足条件的数据的ID，然后根据id 批量去update操作。