ORACLE大数据量下多表关联更新速度慢，怎样提高效率？
2017年08月28日 码农之路 12,165 次浏览
近日在操作中需要将某个表的数据通过另一个表的数据来批量更新，因为数据量大，试了N多种方法（修改子查询、增加索引、删除索引、禁用触发器等等）都不理想，先看看最先的SQL如下：
update TF_USERS tt1
set (DGSID, VDATE) = (
select t4.dgsid, t4.vdate from(
select max(t2.dgsid) dgsid, t2.pid, t2.vdate from
(select t1.pid, max(t1.VDATE) VDATE from HC_DIAGNOSIS t1
where t1.pid in(
SELECT a1.pid FROM TF_USERS a1
left JOIN HC_DIAGNOSIS a2 ON a1.DGSID = a2.DGSID
where a1.dgsid is not null and a2.DGSID is null and a1.pid < 903425000
)
group by t1.PID) t3
left join HC_DIAGNOSIS t2 on T2.pid = t3.pid and T2.VDATE = T3.VDATE
group by t2.pid, t2.vdate
) t4 where tt1.pid = t4.pid
)
where tt1.pid in(
SELECT a1.pid FROM TF_USERS a1
left JOIN HC_DIAGNOSIS a2 ON a1.DGSID = a2.DGSID
where a1.dgsid is not null and a2.DGSID is null and a1.pid < 903425000
)受影响的行: 624
时间: 124.632s以上SQL平均每条持行时间需要0.2秒，然而我的数据库中有30多万数据需要更新，粗略计算下需要十几个小时来计算了，天啦！！！这怎么能行呢？通过简单的测试分析了下原因，主要是数据量太大、查询太复杂、消耗内存（这个只是我的猜测），最后还是决定用存储过程来实现。
然后上网找有没有简单的方法，突然眼前一亮，发现了“快速游标更新法”——它可以支持复杂逻辑的查询语句，更新准确，无论数据多大更新效率依然很高，但执行后不返回影响行数。这不就是我需要的方法么。具体格式如下：
begin
for cr in (查询语句) loop --循环
update table_name set ... --更新语句
end loop; --结束循环
end;原理就是先查询出来需要更新的数据，然后通过循环去更新数据，这样每次只需更新一条记录，速度自然快，以下是修改后的SQL，速度杠杠滴。
begin
for cr in (
select t4.pid, t4.dgsid, t4.vdate from (
select max(t2.dgsid) dgsid, t2.pid, t2.vdate from
(select t1.pid, max(t1.VDATE) VDATE from HC_DIAGNOSIS t1
where t1.pid in(
SELECT a1.pid FROM TF_USERS a1
left JOIN HC_DIAGNOSIS a2 ON a1.DGSID = a2.DGSID
where a1.dgsid is not null and a2.DGSID is null and a1.pid < 903425000
)
group by t1.PID) t3
left join HC_DIAGNOSIS t2 on T2.pid = t3.pid and T2.VDATE = T3.VDATE
group by t2.pid, t2.vdate
) t4
) loop --循环
update TF_USERS set DGSID = cr.dgsid, VDATE = cr.vdate where pid = cr.pid;
end loop; --结束循环
end;受影响的行: 1
时间: 4.151s
>>> Hello World <<<
这篇内容是否帮助到你了呢？
如果你有任何疑问或有建议留给其他朋友，都可以给我留言。
目前有有一条留言：
批量修改的sql大数据时很慢 ，快速游标更新法不错，可以减少很多时间
ps，网站做得不错~