ITPub博客

首页 > 数据库 > Oracle > 两个超大表做关联更新的优化

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

原创 Oracle 作者:david3389 时间:2010-12-17 13:43:01 0 删除 编辑

今天同事给了两个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;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16628454/viewspace-1043225/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ORA-12838的问题
下一篇: SOLARIS维护命令
请登录后发表评论 登录
全部评论

注册时间:2008-11-17

  • 博文量
    19
  • 访问量
    120503