表HC_DATASET 有500万行,HC_GA_BACK_NOR 有10万行
方案1:
update HC_DATASET ts set
STATUS=(select 23 from (select t.sfz, t.name, t.cun
from HC_GA_BACK_NOR t
-- where t.jxsj > trunc(sysdate)
) cldx
where ts.mf15 = cldx.sfz
and ts.MF11 = cldx.name
and ts.status = 22);
STATUS=(select 23 from (select t.sfz, t.name, t.cun
from HC_GA_BACK_NOR t
-- where t.jxsj > trunc(sysdate)
) cldx
where ts.mf15 = cldx.sfz
and ts.MF11 = cldx.name
and ts.status = 22);
10个小时执行不完。
方案2:先查出要更新的结果集,然后按段更新
declare
i int;
begin
i := 0;
i int;
begin
i := 0;
--先把要更新的结果集取出来,因为是loop,所以,Oracle不会一次把所有的查询结果都取出,会分段取
for aa in (select ts.id
from (select t.sfz, t.name, t.cun
from HC_GA_BACK_NOR t
-- where t.jxsj > trunc(sysdate)
) cldx,
HC_DATASET ts
where ts.mf15 = cldx.sfz
and ts.MF11 = cldx.name
and ts.status = 22) loop
update HC_DATASET
set status = 23
where id = aa.id;
i := i + 1;
if mod(i, 1000)=0 then
commit;
dbms_output.put_line(to_char(i));
end if;
end loop;
end;
for aa in (select ts.id
from (select t.sfz, t.name, t.cun
from HC_GA_BACK_NOR t
-- where t.jxsj > trunc(sysdate)
) cldx,
HC_DATASET ts
where ts.mf15 = cldx.sfz
and ts.MF11 = cldx.name
and ts.status = 22) loop
update HC_DATASET
set status = 23
where id = aa.id;
i := i + 1;
if mod(i, 1000)=0 then
commit;
dbms_output.put_line(to_char(i));
end if;
end loop;
end;
40秒
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/263455/viewspace-757610/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/263455/viewspace-757610/