从另一个表更新表时的性能问题 [英] 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屋！