两个超大表做关联更新的优化 之 批量更新

http://blog.itpub.net/16628454/viewspace-1043225/

两个超大表做关联更新的优化2010-12-17 13:43:01

分类： Oracle

今天同事给了两个SQL，超级大，一个表8000多万，一个表7800万，原语句如下：

[@more@]

update CHANNEL_CHENGDU.o_user_CONS partition(p201011) A
SET unuser_flag = (SELECT unuser_flag
FROM CHANNEL_CHENGDU.o_user partition(p201011) B
WHERE A.SERV_ID = B.SERV_ID)
where EXISTS (SELECT 1
FROM CHANNEL_CHENGDU.o_user partition(p201011) B
WHERE A.SERV_ID = B.SERV_ID);

update CHANNEL_CHENGDU.o_user_CONS partition(p201011) A
SET BILL_USER_FLAG = (SELECT BILL_USER_FLAG
FROM CHANNEL_CHENGDU.o_user partition(p201011) B
WHERE A.SERV_ID = B.SERV_ID)
where EXISTS (SELECT 1
FROM CHANNEL_CHENGDU.o_user partition(p201011) B
WHERE A.SERV_ID = B.SERV_ID);

这两条语句在执行第一条的时候执行了6个小时没出结果，就被杀掉了，光回滚就用了2个小时，当时开发人员急于要结果，于是我用以前看到的一篇帖子中提到的方法（http://www.itpub.net/thread-1052077-1-7.html）来解决，其实还是bulk connect，第一条语句跑了899秒，第二条跑了708秒。经过开发人员的逻辑验证，结果是正确的，但我始终对业务逻辑产生怀疑，不过这是后话，毕竟自己对应用不了解，好吧，看下改后的语句。

-----执行了889秒
declare
maxrows number default 5000;
row_id_table dbms_sql.Urowid_Table;
p_id_table dbms_sql.Number_Table;
cursor acnt_first_cur is
SELECT /* use_hash(A,B) parallel(A 4) parallel(B 4) */
b.unuser_flag, b.rowid row_id
FROM CHANNEL_CHENGDU.o_user partition(p201011) B,
CHANNEL_CHENGDU.o_user_CONS partition(p201011) A
WHERE A.SERV_ID = B.SERV_ID
and EXISTS (SELECT /*+use_hash(C,D) parallel(C 4) parallel(D 4)*/
1
FROM CHANNEL_CHENGDU.o_user partition(p201011) C,
CHANNEL_CHENGDU.o_user_CONS partition(p201011) D
WHERE C.SERV_ID = D.SERV_ID)
order by b.rowid;
begin
open acnt_first_cur;
loop
exit when acnt_first_cur%notfound;
fetch acnt_first_cur bulk collect
into p_id_table, row_id_table limit maxrows;
forall i in 1 .. row_id_table.count
update CHANNEL_CHENGDU.o_user_CONS partition(p201011)
set unuser_flag = p_id_table(i)
where rowid = row_id_table(i);
commit;
end loop;
end;
/

-----708秒
declare
maxrows number default 5000;
row_id_table dbms_sql.Urowid_Table;
p_id_table dbms_sql.Number_Table;
cursor acnt_first_cur is
SELECT /* use_hash(A,B) parallel(A 4) parallel(B 4) */
b.BILL_USER_FLAG, b.rowid row_id
FROM CHANNEL_CHENGDU.o_user partition(p201011) B,
CHANNEL_CHENGDU.o_user_CONS partition(p201011) A
WHERE A.SERV_ID = B.SERV_ID
and EXISTS (SELECT /*+use_hash(C,D) parallel(C 4) parallel(D 4)*/
1
FROM CHANNEL_CHENGDU.o_user partition(p201011) C,
CHANNEL_CHENGDU.o_user_CONS partition(p201011) D
WHERE C.SERV_ID = D.SERV_ID)
order by b.rowid;
begin
open acnt_first_cur;
loop
exit when acnt_first_cur%notfound;
fetch acnt_first_cur bulk collect
into p_id_table, row_id_table limit maxrows;
forall i in 1 .. row_id_table.count
update CHANNEL_CHENGDU.o_user_CONS partition(p201011)
set BILL_USER_FLAG = p_id_table(i)
where rowid = row_id_table(i);
commit;
end loop;
end;
/