oracle sql优化 update -> merge

需求：

     下面这个sql跑到时间要花几个小时，数据量：

 

  'EIM_ACCOUNT'   COUNT(*)

 

eim_account 243296

s_addr_org  1434423

s_org_ext   1157261

s_timezone  75

 

 

 

UPDATE siebel.eim_account eim

   SET addr_addr_name =

          (SELECT addr.addr_name

             FROM siebel.s_addr_org addr

                , siebel.s_org_ext org

                , siebel.s_timezone tz

            WHERE addr.ou_id = org.row_id

              AND addr.x_time_zone_id = tz.row_id(+)

              AND NVL (addr.active_flg, ' ')         = NVL (eim.addr_active_flg, ' ')

              AND NVL (addr.x_metro_ind_flg, ' ')     = NVL (eim.addr_x_metroindflg, ' ')

              AND NVL (org.loc, ' ')                 = NVL (eim.loc, ' ')

              AND NVL (org.NAME, ' ')                 = NVL (eim.NAME, ' ')

              AND NVL (addr.addr_type_cd, ' ')         = NVL (eim.addr_addr_type_cd, ' ')

              AND NVL (addr.city, ' ')                 = NVL (eim.addr_city, ' ')

              AND NVL (addr.country, ' ')             = NVL (eim.addr_country, ' ')

              AND NVL (addr.county, ' ')             = NVL (eim.addr_county, ' ')

              AND NVL (addr.province, ' ')             = NVL (eim.addr_province, ' ')

              AND NVL (tz.NAME, ' ')                 = NVL (eim.addr_xtimetimezone, ' ')

              AND NVL (addr.state, ' ')             = NVL (eim.addr_state, ' ')

              AND NVL (addr.zipcode, ' ')           = NVL (eim.addr_zipcode, ' ')

              AND NVL (addr.addr_line_2, ' ')       = NVL (eim.addr_addr_line_2, ' ')

              AND NVL (addr.addr_line_3, ' ')       = NVL (eim.addr_addr_line_3, ' ')

              AND NVL (addr.x_addr_line_4, ' ')     = NVL (eim.addr_x_addr_line_4, ' ')

              AND NVL (addr.x_addr_line_5, ' ')     = NVL (eim.addr_x_addr_line_5, ' ')

              AND NVL (addr.addr, ' ')              = NVL (eim.addr_addr, ' ')

              AND ROWNUM < 2)

 WHERE EXISTS (

          SELECT 1

            FROM siebel.s_addr_org addr

               , siebel.s_org_ext org

               , siebel.s_timezone tz

           WHERE addr.ou_id = org.row_id

             AND addr.x_time_zone_id = tz.row_id(+)

               AND NVL (addr.active_flg, ' ')         = NVL (eim.addr_active_flg, ' ')

              AND NVL (addr.x_metro_ind_flg, ' ')     = NVL (eim.addr_x_metroindflg, ' ')

              AND NVL (org.loc, ' ')                 = NVL (eim.loc, ' ')

              AND NVL (org.NAME, ' ')                 = NVL (eim.NAME, ' ')

              AND NVL (addr.addr_type_cd, ' ')         = NVL (eim.addr_addr_type_cd, ' ')

              AND NVL (addr.city, ' ')                 = NVL (eim.addr_city, ' ')

              AND NVL (addr.country, ' ')             = NVL (eim.addr_country, ' ')

              AND NVL (addr.county, ' ')             = NVL (eim.addr_county, ' ')

              AND NVL (addr.province, ' ')             = NVL (eim.addr_province, ' ')

              AND NVL (tz.NAME, ' ')                 = NVL (eim.addr_xtimetimezone, ' ')

              AND NVL (addr.state, ' ')             = NVL (eim.addr_state, ' ')

              AND NVL (addr.zipcode, ' ')           = NVL (eim.addr_zipcode, ' ')

              AND NVL (addr.addr_line_2, ' ')       = NVL (eim.addr_addr_line_2, ' ')

              AND NVL (addr.addr_line_3, ' ')       = NVL (eim.addr_addr_line_3, ' ')

              AND NVL (addr.x_addr_line_4, ' ')     = NVL (eim.addr_x_addr_line_4, ' ')

              AND NVL (addr.x_addr_line_5, ' ')     = NVL (eim.addr_x_addr_line_5, ' ')

              AND NVL (addr.addr, ' ')              = NVL (eim.addr_addr, ' ');

 

 改用merge写法如下，cost变为原来的1/10000；时间变为17分钟。 

merge into siebel.eim_account eim

 using (SELECT

  addr.addr_name

 ,eim.addr_active_flg

 ,eim.addr_x_metroindflg

 ,eim.loc

 ,eim.NAME

 ,eim.addr_addr_type_cd

 ,eim.addr_city

 ,eim.addr_country

 ,eim.addr_county

 ,eim.addr_province

 ,eim.addr_xtimetimezone

 ,eim.addr_state

 ,eim.addr_zipcode

 ,eim.addr_addr_line_2

 ,eim.addr_addr_line_3

 ,eim.addr_x_addr_line_4

 ,eim.addr_x_addr_line_5

 ,eim.addr_addr

  FROM siebel.eim_account eim

     , siebel.s_addr_org addr

     , siebel.s_org_ext org

     , siebel.s_timezone tz

 WHERE addr.ou_id = org.row_id

   AND addr.x_time_zone_id = tz.row_id(+)

   AND NVL (addr.active_flg, ' ')         = NVL (eim.addr_active_flg, ' ')

   AND NVL (addr.x_metro_ind_flg, ' ')    = NVL (eim.addr_x_metroindflg, ' ')

   AND NVL (org.loc, ' ')                       = NVL (eim.loc, ' ')

   AND NVL (org.NAME, ' ')                      = NVL (eim.NAME, ' ')

   AND NVL (addr.addr_type_cd, ' ')       = NVL (eim.addr_addr_type_cd, ' ')

   AND NVL (addr.city, ' ')               = NVL (eim.addr_city, ' ')

   AND NVL (addr.country, ' ')                  = NVL (eim.addr_country, ' ')

   AND NVL (addr.county, ' ')                   = NVL (eim.addr_county, ' ')

   AND NVL (addr.province, ' ')           = NVL (eim.addr_province, ' ')

   AND NVL (tz.NAME, ' ')                       = NVL (eim.addr_xtimetimezone, ' ')

   AND NVL (addr.state, ' ')              = NVL (eim.addr_state, ' ')

   AND NVL (addr.zipcode, ' ')                  = NVL (eim.addr_zipcode, ' ')

   AND NVL (addr.addr_line_2, ' ')        = NVL (eim.addr_addr_line_2, ' ')

   AND NVL (addr.addr_line_3, ' ')        = NVL (eim.addr_addr_line_3, ' ')

   AND NVL (addr.x_addr_line_4, ' ')      = NVL (eim.addr_x_addr_line_4, ' ')

   AND NVL (addr.x_addr_line_5, ' ')      = NVL (eim.addr_x_addr_line_5, ' ')

   AND NVL (addr.addr, ' ')               = NVL (eim.addr_addr, ' ')

   AND eim.if_row_batch_num between 700000 and 700999

   --AND ROWNUM < 2

   ) t

on(

            nvl(t.addr_active_flg   ,' ') =     nvl(eim.addr_active_flg       ,' ')

 and  nvl(t.addr_x_metroindflg,' ') =     nvl(eim.addr_x_metroindflg    ,' ')

 and    nvl(t.loc                ,' ') =    nvl(eim.loc                    ,' ')

 and    nvl(t.NAME                ,' ') =    nvl(eim.NAME                ,' ')

 and    nvl(t.addr_addr_type_cd    ,' ') =    nvl(eim.addr_addr_type_cd    ,' ')

 and    nvl(t.addr_city            ,' ') =    nvl(eim.addr_city            ,' ')

 and    nvl(t.addr_country         ,' ') =    nvl(eim.addr_country         ,' ')

 and    nvl(t.addr_county         ,' ') =    nvl(eim.addr_county         ,' ')

 and    nvl(t.addr_province        ,' ') =    nvl(eim.addr_province        ,' ')

 and    nvl(t.addr_xtimetimezone,' ') =    nvl(eim.addr_xtimetimezone    ,' ')

 and    nvl(t.addr_state        ,' ') =    nvl(eim.addr_state            ,' ')

 and    nvl(t.addr_zipcode        ,' ') =    nvl(eim.addr_zipcode        ,' ')

 and    nvl(t.addr_addr_line_2    ,' ') =    nvl(eim.addr_addr_line_2    ,' ')

 and    nvl(t.addr_addr_line_3    ,' ') =    nvl(eim.addr_addr_line_3    ,' ')

 and    nvl(t.addr_x_addr_line_4,' ') =    nvl(eim.addr_x_addr_line_4    ,' ')

 and    nvl(t.addr_x_addr_line_5,' ') =    nvl(eim.addr_x_addr_line_5    ,' ')

 and    nvl(t.addr_addr            ,' ') =    nvl(eim.addr_addr            ,' ')

 )

when matched then update

set eim.addr_addr_name = t.addr_name

WHERE eim.if_row_batch_num between 700000 and 700999

;