oracle 大表新增列 慢_ORACLE大数据量下多表关联更新速度慢，怎样提高效率？

祝大家新年快乐，有任何问题可与我联系：

近日在操作中需要将某个表的数据通过另一个表的数据来批量更新，因为数据量大，试了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 &#