公司有个任务要更新数据,主要SQL是这样的:update user t set t.createDate =(select to_date(substr(trim(min(t1.time)),1,14),'yyyymmddhh24miss') from login t1 where t1.telnum=t.tenum),user是用户表,有1600W数据,login是登陆记录表,有30000W数据,从login表中按用户手机号取出最早的登陆时间填到user表中,索引都建好了,内部封闭数据库,只有一台数据库服务器跑,不能借用外部硬件资源,问怎么能让他跑的再快点儿。运维走了,老大扔给了我。。
邀请回复的给位大神是系统推荐的,若有叨扰,敬请见谅。
邀请回复的给位大神是系统推荐的,若有叨扰,敬请见谅。
可以借鉴spark,也就是大数据处理。 把数据导出成文本 ,让spark对文件进行切割查找最近的登录时间。
create table temp as
select t.rowid v_rowid,t1.col from user t ,login t1 where t1.telnum=t.tenum;
然后用temp表
merge into user t
using (SELECT * FROM temp t1 WHERE ROWNUM<100001)
on (t.rowid=t1.v_rowid)
when matched then update t.XX=t1.XX;
DELETE FROM temp t1 WHERE ROWNUM<100001;
COMMIT;
整个merge,delete操作很快,ROWNUM<100001;你也可以自己选择多少次一提交,必须保证不要因为锁行而影响正常业务运行,同时可以打开另一个session查看temp表的数据量来看更新速度。
更进一步的方法是将是temp表分割成5个表,开5窗口同时跑数据。
以前更新一个上亿数据的表,筛选条件的临时表都在300万左右,我是使用rownum<500000,每次更新差不多30-50,300万更新完花费4分钟多一点,你可以参考一下~
同样你也可以使用游标,使用fetch bulk collect into 的语法批量修改,无论如何核心都是通过rowid字段能快速查找数据进行修改~
l_index number ;
begin
l_index := 0 ;
for rs in (select uerid, telnum from user) loop
update user set createDate = (select to_date(substr(trim(min(t1.time)),1,14),'yyyymmddhh24miss') from login t1 where t1.telnum= rs.telnum) where userid = rs.id;
l_index := l_index + 1 ;
if mod(l_index,100) = 0 then -- 每100条提交一次
commit;
end if ;
end loop ;
commit ;
end ;