请高手帮忙优化一下,update速度好慢啊.thanks

[hutiefeng9847]

define acct_item_t=acct_item_t_0603;
define serv_addr=serv_addr_0401;
DECLARE
CURSOR S_CUR IS select * From &acct_item_t where exch_id='0';
S S_CUR%ROWTYPE;
i number;
BEGIN
OPEN S_CUR;
LOOP
FETCH S_CUR INTO S;
EXIT WHEN S_CUR%NOTFOUND;
begin
begin
select distinct exch_id into i from &serv_addr
where serv_id=s.serv_id;
EXCEPTION
WHEN OTHERS THEN
i:=1;
end;
update &acct_item_t set exch_id=i where serv_id=s.serv_id;
commit;
EXCEPTION
WHEN OTHERS THEN
null;
end;
END LOOP;
CLOSE S_CUR;
END;
/

[qiaozhiwei]

最好不要用cursor，用cursor很影响效率

[Renkey]

对 exch_id建立一个索引.

[bugchen888]

UPDATE &acct_item_t t
SET exch_id=(SELECT exch_id FROM &serv_addr a WHERE a.serv_id=t.serv_id)
WHERE t.exch_id='0'
AND t.serv_id IN (SELECT serv_id FROM &serv_addr)

UPDATE &acct_item_t
SET exch_id=1
WHERE t.exch_id='0'
AND t.serv_id NOT IN (SELECT serv_id FROM &serv_addr)

[sanoul]

如果update的字段exch_id存在索引,则先去掉索引,完成全部数据之后,重建索引