从另一个表更新表时的性能问题 [英] Performance Issue when Updating Table from Another Table
本文介绍了从另一个表更新表时的性能问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图用新数据更新旧客户数据,所以基本上我是用new_customer_source表的名字和姓氏更新old_customer_source表的名字和姓氏.
I'm trying to update old customers data with new data, so basically I'm updating firstname and lastname of old_customer_source table with firstname and lastname of new_customer_source table.
我为new_customer_source的监护人建立了索引,但是我没有特权为old_customer_source的监护人建立索引.
I indexed custid of new_customer_source but I don't have privileges to index old_customer_source's custid.
要更新的记录总数约为5万,并且查询要花30多分钟才能运行!
Total records to updated is ~50k and they query is taking over 30 mins to run!
您对以下如何改进给定的Oracle查询有任何建议吗?
Do you have any suggestions on how to improve the given Oracle query below?
update old_customer_source t1
set (t1.firstname, t1.lastname) =
(
select t2.firstname, t2.lastname
from new_customer_source t2
where t1.custid = t2.custid
)
where exists ( select 'x'
from new_customer_source t3
where t1.custid = t3.custid
)
推荐答案
尝试使用合并.
merge into old_customer_source t1
using (select t2.custid, t2.firstname, t2.lastname
from new_customer_source t2
) t2
on (t1.custid = t2.custid)
when matched then
update set t1.firstname = t2.firstname,
t1.lastname = t2.lastname
;
这篇关于从另一个表更新表时的性能问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文