更新語句的效率比較（merge into ）

    昨晚更新了一批資料，用update的老辦法耗時20多分，而用 merge into 不到2秒結束，效率真是天壤之別。具體見下：

     用T_TMP_SCHOOL135868行）的BIRTH 欄位更新T_TMP_NT_CUSTOMERDETAIL 763119行） 的BIRTHDATE 欄位，串連條件 T_TMP_SCHOOL.ID = t_tmp_nt_customerdetail.SCHOOLID

--表結構
create table T_TMP_NT_CUSTOMERDETAIL
(
  CUSTOMERID         VARCHAR2(15) not null,
  DOCCATEGORY        VARCHAR2(2) not null,
  DOCNUMBER          VARCHAR2(20) not null,
  BIRTHDATE          VARCHAR2(8),
  ...........
  SCHOOLID           VARCHAR2(60)
);

create table T_TMP_SCHOOL
(
  ID      VARCHAR2(20),
  COMPANY VARCHAR2(100),
  NAME    VARCHAR2(20),
  BIRTH   VARCHAR2(20)
);

--兩個表的資料見下：
select count(1) from t_tmp_nt_customerdetail t;  --763119
select count(1) from  t_tmp_school;              --135868

--為了驗證結果，測試前先清空birthdate的值，共更改 135879 行
update  t_tmp_nt_customerdetail t   
set t.birthdate = null
where t.schoolid is not null;

---實現的過程：
create or replace procedure p_tmp_update_customerdetail
is
  v_BeginTran INT := 0;    -- 事務標誌,初始值為0，表示沒有事務
  v_ErrCode   INT;
  v_ErrMsg    VARCHAR2(200);   -- 處理異常變數

begin
   -- 設定事務標誌為1，表示開始事務
  v_BeginTran := 1;

  merge into t_tmp_nt_customerdetail t
  using (select b.id, b.birth from t_tmp_school b where b.birth is not null) a
     on (t.schoolid = a.id)
   when matched then
     update set t.birthdate = a.birth where t.schoolid　is not null;

   COMMIT;
    -- 提交事務並且置事務標誌為0。
    v_BeginTran := 0;

EXCEPTION
  WHEN OTHERS THEN
    -- 如果異常，復原事務。
    IF v_BeginTran = 1 THEN
      ROLLBACK;
    END IF;
     v_ErrCode := SQLCODE;
    v_ErrMsg  := SUBSTR(SQLERRM, 1, 150);
    dbms_output.put_line(v_ErrCode);
    dbms_output.put_line(v_ErrMsg);
end;

--執行過程，用時1.11秒
SQL> exec  p_tmp_update_customerdetail;

--再次驗證結果，先前清空birthdate的值已經有了，返回 135879 行
select count(1) from t_tmp_nt_customerdetail t  
where  t.schoolid is not null
and  t.birthdate is not  null;

--而用下面類似的語句，這些資料執行了24分鐘多：
update t_tmp_nt_customerdetail t
   set t.birthdate = (select b.birth
                        from t_tmp_school b
                       where t.schoolid = b.id)
 where t.schoolid =
       (select c.id from t_tmp_school c where t.schoolid = c.id)
   and t.schoolid is not null;     ---註：為什麼要寫這個羅嗦的條件呢？因為沒有這個條件就把整個表的資料全部更新了，因此必須寫，所以大家應該多實踐，不要被一些表面現象所蒙蔽。
 

 

 

 

本文出自 “srsunbing” 部落格，請務必保留此出處http://srsunbing.blog.51cto.com/3221858/1130337