有两张表记录都约为200万条,进行更新操作,语句如下:
update infotemp set occurdate = '2011/04/07',bal = (select bal from tempnew a where infotemp.acno=a.acno) where exists (select 1 from tempnew c where infotemp.acno = c.acno)表索引是以acno为唯一字段感觉执行了好长时间,有没有用其他方法更快一点?
update infotemp set occurdate = '2011/04/07',bal = (select bal from tempnew a where infotemp.acno=a.acno) where exists (select 1 from tempnew c where infotemp.acno = c.acno)表索引是以acno为唯一字段感觉执行了好长时间,有没有用其他方法更快一点?
set (occurdate,bal) =
(select '2011/04/07',a.bal from tempnew a
where exists ( select 1 from infotemp b where a.accno=b.accno )
)
再看下执行计划,如果更新的条数不多的话,加提示。如果速度还特别慢的话,
create table tmp1
as ...
在create 过程中更新,然后rename tmp1 to infotemp,建立索引加限制
update deposit_accumulate_infotemp set (occurdate,xd05bal) =(select '2011/03/24',a.xd05bal from deposit_accumulate_tempnew a
where exists (select 1 from deposit_accumulate_infotemp b where a.xd05acno = b.xd05acno))执行计划如下:
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=2805 Cardinality=1675694 Bytes=25135410 IO cost=2736 Time=34
UPDATE Object owner=TLCZ Object name=DEPOSIT_ACCUMULATE_INFOTEMP
TABLE ACCESS FULL Object owner=TLCZ Object name=DEPOSIT_ACCUMULATE_INFOTEMP Cost=2805 Cardinality=1675694 Bytes=25135410 IO cost=2736 Time=34
NESTED LOOPS Cost=864004 Cardinality=806559 Bytes=37101714 IO cost=861537 Time=10369
SORT UNIQUE Cost=9165 Cardinality=1675694 Bytes=38540962 IO cost=9097 Time=110
INDEX FULL SCAN Object owner=TLCZ Object name=PK_INFOTEMP_ACNO Cost=9165 Cardinality=1675694 Bytes=38540962 IO cost=9097 Time=110
TABLE ACCESS BY INDEX ROWID Object owner=TLCZ Object name=DEPOSIT_ACCUMULATE_TEMPNEW Cost=2 Cardinality=1 Bytes=23 IO cost=2 Time=1
INDEX UNIQUE SCAN Object owner=TLCZ Object name=IDX_DAT_IX1_NEW Cost=1 Cardinality=1 IO cost=1 Time=1
另外一条是这样:
update deposit_accumulate_infotemp set occurdate='2011/03/24',xd05bal=(select xd05bal from deposit_accumulate_tempnew a where deposit_accumulate_infotemp.xd05acno=a.xd05acno)
where exists (select 1 from deposit_accumulate_tempnew c where deposit_accumulate_infotemp.xd05acno= c.xd05acno)执行计划如下:
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=9581 Cardinality=1158610 Bytes=63723550 IO cost=9381 Time=115
UPDATE Object owner=TLCZ Object name=DEPOSIT_ACCUMULATE_INFOTEMP
HASH JOIN RIGHT SEMI Cost=9581 Cardinality=1158610 Bytes=63723550 IO cost=9381 Time=115
INDEX FAST FULL SCAN Object owner=TLCZ Object name=IDX_DAT_IX1_NEW Cost=1164 Cardinality=1166524 Bytes=22163956 IO cost=1134 Time=14
TABLE ACCESS FULL Object owner=TLCZ Object name=DEPOSIT_ACCUMULATE_INFOTEMP Cost=2805 Cardinality=1675694 Bytes=60324984 IO cost=2736 Time=34
TABLE ACCESS BY INDEX ROWID Object owner=TLCZ Object name=DEPOSIT_ACCUMULATE_TEMPNEW Cost=3 Cardinality=1 Bytes=23 IO cost=3 Time=1
INDEX UNIQUE SCAN Object owner=TLCZ Object name=IDX_DAT_IX1_NEW Cost=2 Cardinality=1 IO cost=2 Time=1不知道哪条好一点,执行计划有点看不懂
1. 表索引是以acno为唯一字段, 如果accno是主键或者唯一索引的话,就是2表连接的字段,(不是主键或者唯一索引会报错)
这样写,绝对快
update (select a.occurdate
,a.xd05bal bal1
,b.xd05bal bal2
from deposit_accumulate_infotemp a, deposit_accumulate_tempnew b
where a.xd05acno = b.xd05acno
) x set x.occurdate = '2011/03/24'
,x.bal1 = x.bal22. 或者使用rowid更新
eg:
declare cursor c1 is select a.rowid rd,b.xd05bal
from deposit_accumulate_infotemp a, deposit_accumulate_tempnew b
where a.xd05acno = b.xd05acno;
rid varchar2(20);
bal deposit_accumulate_tempnew%type;
begin
open c1;
loop
fetch c1 into rid,bal;
update deposit_accumulate_infotemp a set a.occurdate='2011/03/24', a.xd05bal=bal
where a.rowid=rid
;
exit when c1%notfound;
end loop;
commit;
end;
/3. 或者就是
create table tmp1
as ...
在create 过程中更新,然后drop 原表,rename tmp1 to infotemp,建立索引加限制