Oracle SQL性能优化 – 根据大表关联更新小表

需求：

　　小表数据量20w条左右，大表数据量在4kw条左右，需要根据大表筛选出150w条左右的数据并关联更新小表中5k左右的数据。

性能问题：

对筛选条件中涉及的字段加index后，如下常规的update语句仍耗时半小时左右。

  UPDATE WMOCDCREPORT.DM_WM_TRADINGALL A
  SET
  (
    A.RELATIONSHIPNO,
    A.PACKAGE
  )
  =
  (SELECT
                B.RELATIONSHIPNO,
    CASE
                                WHEN (B.SEGMENTCODE=''
                                                OR B.SEGMENTCODE   =''
                                                OR B.SEGMENTCODE   =''
                                                OR B.SEGMENTCODE   ='')
                                                THEN 'BC'
                                WHEN (B.SEGMENTCODE='')
                                                THEN 'PW'
                                WHEN (B.SEGMENTCODE='')
                                                THEN 'MM'
                                WHEN (B.SEGMENTCODE='')
                                                THEN 'EB'
                                WHEN (B.SEGMENTCODE='')
                                                THEN 'PB'
                                ELSE B.SEGMENTCODE
    END
    FROM DATACORE.DF_CUST_HISTORY B
    WHERE B.ACCOUNT_NO=A.SETTLEMENTACCOUNT
    AND B.DATA_DATE = '2018-11-30'
    AND rownum        = 1
  )
  WHERE A.MONTH = 'SEP'
  AND A.DATA_DATE = '2018-09-30'
  AND EXISTS
  (
    SELECT 1 FROM DATACORE.DF_CUST_HISTORY C
    WHERE C.ACCOUNT_NO=A.SETTLEMENTACCOUNT
    AND C.DATA_DATE = '2018-11-30'
  );

经过数次搜索，发现同关联更新有关的技术博客基本上是更新大表数据，比如here.(使用批量更新)。

也分析过执行计划，同预想的性能瓶颈一样，主要由以下两个方面造成

(1) DATACORE.DF_CUST_HISTORY数据量太大，本想将某一天的数据select出来提前插入到一张表中，但估计效果不会太明显，因为插入150w条数据本身也会耗时很长。

(2) 需要更新5k条数据，且每条数据需要到150w条数据中做关联查询（时间主要耗在这）。

性能优化：

小表5k，大表150w，理所应当想到采用join的方式并保留小表中的数据。接下来是怎么把join后的数据更新到小表中(不用update)？merge into!

这里还涉及到一个小问题，merge into中的on条件需要保证一一对应，而大表中很可能出现重复的ACCOUNT_NO，所以需要排重，怎么做？用partition by !

另外，关于join中on条件和where条件的比较。

优化后的sql（运行时间8-10s）：

merge into wmocdcreport.dm_wm_tradingall a
using (
    select
       t.rid,
       t.settlementaccount,
       tx.relationshipno,
       case
         when (tx.segmentcode = '' or tx.segmentcode = '' or
              tx.segmentcode = '' or tx.segmentcode = '') then
          'BC'
         when (tx.segmentcode = '') then
          'PW'
         when (tx.segmentcode = '') then
          'MM'
         when (tx.segmentcode = '') then
          'EB'
         when (tx.segmentcode = '') then
          'PB'
         else
          tx.segmentcode
       end as package
    from (
        select rowid rid,
            dwt.settlementaccount
        from wmocdcreport.dm_wm_tradingall dwt
        where dwt.month = 'SEP'
        and dwt.data_date = '2018-09-30'
    ) t
    inner join
    (
        select row_number() over (partition by c.account_no order by c.relationshipno) seq,
              c.account_no,
              c.relationshipno,
              c.segmentcode
        from datacore.df_cust_history c
        where c.data_date = '2018-11-30'
    ) tx
    on tx.account_no = t.settlementaccount and tx.seq = 1
) b on (a.rowid = b.rid)
when matched then
   update set a.relationshipno = b.relationshipno,
              a.package        = b.package;