Oracle大数据量更新方法
实际工作中我们有时会遇到，需要对一些超级大（BT）的表进行数据更新（更悲剧的是有时需要关联更新，就是依据一张大表的数据来更新另一张表的数据）我们通常的更新方法可能在性能效率上遭遇瓶颈，下面就笔者遇到的一个案列进行记录分享。
首先我们来看下需求：
一张玩家首登表：account_first_login（此表中有account,login_date,login_region字段）一张玩家每日活跃表：account_active_day（有account,active_date,login_region）
现在的需求是需要把首登表account_first_login中的login_region的值更新为对应的每日活跃表：account_active_day中的login_region值，有同事最初的写法是：
| 1 2 3 4 5 6 7 |
alter session enable parallel dml;
update /*+ parallel(t1,4) */ account_first_login t1
set login_region=
(select login_region
from account_active_day t2
where t1.account = t2.account
and t1.login_date = t2.active_date);
|
结果5,6个小时过去了 更新还没有完成，后来优化更改为关联update
| 1 2 3 4 5 6 7 |
update (select /*+ bypass_ujvc */
t1.login_region old_id, t2.login_region new_id
from account_first_login t1,
account_active_day t2
where t1.account = t2.account
and t1.login_date = t2.active_date) r
set r.old_id = r.new_id;
|
修改后的写法效率有了一定的提升，2个多小时完成，可是时间还是不能接受，继续优化，想到了通过rowid批量进行更新
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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(t1,t2) parallel(t1,4) parallel(t2,4) */
t2.login_region, t1.rowid
from account_first_login t1, account_active_day t2
where t1.account = t2.account
and t1.login_date = t2.login_date
order by t1.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 account_first_login
set login_region = p_id_table(i)
where rowid = row_id_table(i);
commit;
end loop;
end;
|
结果优化后的更新速度是相当神速啊：247s完成！
结论：当更新大数据量时可以通过rowid进行排序后再批量更新，因为通过rowid进行排序后想应的被更新数据多数在一个数据块上，这样按块进行批量更新速度应该能够大大提高。
from：http://hi.baidu.com/songyunkui/item/04aba589f7ffa705100ef3c1
声明：该文观点仅代表作者本人，牛骨文系教育信息发布平台，牛骨文仅提供信息存储空间服务。
