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脚本很好实现，或者结合临时表，在临时表中增加一个有序列性质的列，从小序列开始往大序列更新，记录更新到的序列号即可。
上一篇： « 新主页开通，欢迎大家光临！
下一篇： 怎么样在P系列上安装Aix 5L操作系统 »
- 发表评论
