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

时间:2022-09-11 13:29:05

需求:

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

性能问题:

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

  UPDATE WMOCDCREPORT.DM_WM_TRADINGALL A
  SET
  (
    A.RELATIONSHIPNO,
    A.PACKAGE
  )
  =
  (SELECT 
                B.RELATIONSHIPNO,
    CASE
                                WHEN (B.SEGMENTCODE='52'
                                                OR B.SEGMENTCODE   ='55'
                                                OR B.SEGMENTCODE   ='56'
                                                OR B.SEGMENTCODE   ='59')
                                                THEN 'BC'
                                WHEN (B.SEGMENTCODE='66')
                                                THEN 'PW'
                                WHEN (B.SEGMENTCODE='60')
                                                THEN 'MM'
                                WHEN (B.SEGMENTCODE='65')
                                                THEN 'EB'
                                WHEN (B.SEGMENTCODE='61')
                                                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 = '52' or tx.segmentcode = '55' or
              tx.segmentcode = '56' or tx.segmentcode = '59') then
          'BC'
         when (tx.segmentcode = '66') then
          'PW'
         when (tx.segmentcode = '60') then
          'MM'
         when (tx.segmentcode = '65') then
          'EB'
         when (tx.segmentcode = '61') 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;