oracle-优化技巧.doc

一、SQLPL 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_db1as 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_dateyyyymmdd); create table account_db1as select b.account_no,a.idauth_passdate ???from bmw_idauth_db1a,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_db1a, 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);