我整理的FAQ第二版，感谢大家的支持，共享出来[@more@]为了感谢大家对我的支持，我现在将我整理的FAQ第二版for oracle共享出来。

一、SQL&PL SQL

怎么样大批量的更新数据而不影响正常业务

1、化整为零
一般情况下，如果需要对一个表进行大批量的更新的时候，由于涉及到的记录数很多，所以可能需要花费的时间也就很长，这种情况下，还采用一个单独的update 语句来更新的话，就会造成长时间的加锁，影响到业务。
简单的一个例子，如要更新im_user表中的非空ID为用户表bmw_users中的ID，关联字段为im_user.login_id=bmw_users.nick，语句可以这样写


update im_user i set i.id=(select id from bmw_users u 
where i.login_id=u.nick)
where i.id is not null;
.

这个语句可以更新到几百万记录，当然，耗费时间可能需要1小时以上，对于im_user这样被频繁更新的表来说，肯定是不现实的，所以，该语句可以改写为如下的PL/SQL块。


declare 
row_num number := 0;
begin
for c_usr in (select login_id from im_user t where id is null) loop
update im_user i set i.id =
(select id from bmw_users u where i.login_id = u.nick)
where login_id = c_usr.login_id;
row_num := row_num + 1;
if mod(row_num,100) =0 then
commit;
end if;
end loop;
commit;
end;
/
.

这样的话，因为每更新100条就提交1次，对表的影响相对是很小的，而且，如果是一个语句，如果中途执行失败，将导致回滚，同样要耗费很长时间，但是这种情况下，因为是一边执行一边提交，基本可以分很多次来操作，之间不会有影响。

2、巧用临时表
很多情况下，需要更新的数据是根据很多条件判断出来的，查询很慢，但是更新的数据本身不多，比较快，这个时候，就可以考虑用临时表，先把需要更新的数据（包括主键）放入到临时表，然后根据主键更新，可能一个UPDATE语句就可以解决问题。
如支付宝迁移时，更新认证表数据：
先创建临时表


create table bmw_idauth_db1_20050704 as 
select a.id,b.idauth_passdate from bmw_users a,bmw_idauth b
where a.nick=b.nick 
and b.status='SUCCESS' 
and b.idauth_passdate>=to_date('20050501','yyyymmdd');

create table account_db1_20050704 as 
select b.account_no,a.idauth_passdate 
from bmw_idauth_db1_20050704 a,bmw_payment_account b 
where a.id=b.user_id 
and b.enabled_status='1';
.

然后根据临时表来更新，因为记录数本身只在查询获得数据比较慢，而这里更新就很快了。


UPDATE (SELECT a.idauth_passdate,
b.id_auth_date,
b.is_id_auth
FROM account_db1_20050704 a, beyond_credit_info b
WHERE a.account_no = b.user_id||'0156') x
SET x.id_auth_date = x.idauth_passdate,
x.is_id_auth ='1';
.

另外一个方面，临时表可以对需要更新的数据做备份，如果发现数据更新错误或者时间，可以回滚。如对需要更新的数据，先创建一个临时备份表出来，这样的话，如果更新失败也可以回滚：


create table tmp_table as select id,name,address from test_table where ……;

update test_table t set name=?,address=?
where id in (select id from tmp_table);
.

或者
--where exists (select null from tmp_table tmp where tmp.id=t.id)
当然，如果临时表的数据量也很大的话，也可以与方法1结合，在临时表中做循环，如
for c_usr in (select id from tmp_table t) loop

其它很多小技巧，如断点继续（也就是更新失败后，不用重新开始，从失败点继续更新）。采用方法1的PL/SQL脚本很好实现，或者结合临时表，在临时表中增加一个有序列性质的列，从小序列开始往大序列更新，记录更新到的序列号即可。

ITPub博客

我整理的FAQ第二版，感谢大家的支持，共享出来

最新文章