批量更新總結

最近遇到一個這麼一個業務:每晚根據另一個系統上報的資料(表 t_taemployee ,100W左右資料),更新正式系統裡面對應名單(表tatable, 2000W左右資料)的狀態等資訊,基於商業原因,這裡的表名及欄位名都已做處理,分別採用別名代替.

實現方式:定義一個procedure,採用Job來定時呼叫procedure執行,其procedure程式碼(已處理過)如下

create or replace procedure upload_state as
  curyear  number;
  curmonth number;
  tatable varchar(100) := 't_test1_';
  fetable varchar(100) := 't_test2_';
  cout     number := 0;
  rowindex number := 0;
  exc_time exception;
  exc_ta exception;
  exc_fe exception;
  insert_err varchar(200) := 'insert into t_synerror2(message) values(:message)';
  type v_cursor is ref cursor;
  y_cursor  v_cursor;
  f_cursor v_cursor;
  query_y   varchar2(1000);
  query_f  varchar2(1000);
  type y_rec is record(
    state  number,
    sje   number,
    row_id varchar2(20));
  type f_rec is record(
    state  number,
    row_id varchar2(20));
  type y_array_type is table of y_rec;
  type f_array_type is table of f_rec;
  y_array  y_array_type;
  f_array f_array_type;
begin
  select count(1)
    into cout
    from user_tables
   where table_name = upper('t_synerror2');
  if cout = 0 then
    execute immediate 'create table t_synerror2(
                      notedate date default sysdate,
                      message varchar(100))';
  end if;

  select extract(year from(sysdate - 1)), extract(month from(sysdate - 1))
    into curyear, curmonth
    from dual;
  if curmonth = 2 or curmonth = 8 then
    raise exc_time;
  end if;

  tatable := tatable || curyear || curmonth;
  select count(1)
    into cout
    from user_tables
   where table_name = upper(tatable);
  if cout = 0 then
    raise exc_ta;
  end if;

  fetable := fetable || curyear || curmonth;
  select count(1)
    into cout
    from user_tables
   where table_name = upper(fetable);
  if cout = 0 then
    raise exc_fe;
  end if;

  --bluck collect
  --
  query_y := 'select ad.state, ad.sje, y.rowid row_id
      from st_goup sj, t_taemployee ad, ' ||
               tatable || ' y
     where sj.js_id = ad.sid
       and sj.state = 1
       and sj.q_id = y.sid';
  open y_cursor for query_y;
  fetch y_cursor bulk collect
    into y_array;
  close y_cursor;
  for r in y_array.first .. y_array.last loop
    execute immediate 'update ' || tatable ||
                      ' y set y.state = :state, y.sje  = :sje where rowid=:rowd'
      using y_array(r).state, y_array(r).sje, y_array(r).row_id;
    rowindex := rowindex + 1;
    if rowindex = 1000 then
      rowindex := 0;
      commit;
    end if;
  end loop;
  commit;
  /*
  execute immediate '
    update ' || tatable || ' y
       set y.state = (select ad.state
                        from st_goup sj, t_taemployee ad
                       where sj.js_id = ad.sid
                         and sj.state = 1
                         and q_id = y.sid),
           y.sje  = (select ad.sje
                        from st_goup sj, t_taemployee ad
                       where sj.js_id = ad.sid
                         and sj.state = 1
                         and q_id = y.sid)
     where exists (select sj.q_id
              from st_goup sj, t_taemployee ad
             where sj.js_id = ad.sid
               and sj.state = 1
               and q_id = y.sid)';
  commit;
  */
  --bluck collect
  --
  query_f := 'select fe.state, ff.rowid
      from st_goup    sj,
           t_femployee      fe,
           ' || fetable || ' ff
     where sj.js_id = fe.sid
       and sj.state = 1
       and q_id = ff.sid';
  open f_cursor for query_f;
  fetch f_cursor bulk collect
    into f_array;
  close f_cursor;
  rowindex := 0;
  for r in f_array.first .. f_array.last loop
    execute immediate 'update ' || fetable || ' f
       set f.state = :state where rowid = :row_id'
      using f_array(r).state, f_array(r).row_id;
    rowindex := rowindex + 1;
    if rowindex = 1000 then
      rowindex := 0;
      commit;
    end if;
  end loop;
  commit;
  /*
  execute immediate '
    update ' || fetable || ' f
       set f.state = (select fe.state
                        from st_goup sj, t_femployee fe
                       where sj.js_id = fe.sid
                         and sj.state = 1
                         and q_id = f.sid)
     where exists (select sj.q_id
              from st_goup sj, t_femployee fe
             where sj.js_id = fe.sid
               and sj.state = 1
               and q_id = f.sid)';
  commit;
  */
exception
  when exc_time then
    execute immediate insert_err
      using '當前系統月份為“' || curmonth || '”，...';
    commit;
  when exc_ta then
    execute immediate insert_err
      using '當前系統年月（' || curyear || '，' || curmonth || '）...“' || tatable || '”還未生成';
    commit;
  when exc_fe then
    execute immediate insert_err
      using '當前系統年月（' || curyear || '，' || curmonth || '）...“' || fetable || '”還未生成';
    commit;
end upload_state;
/

以上是使用bulk collect into優化後的效果,該指令碼通過job呼叫,一分鐘左右執行完成.

而優化前,沒有用到bulk collect into ,而是直接update(註釋掉的指令碼),執行了近5小時,最後不得不手動停止Job執行.

這裡僅僅提供給大家一個批量更新的參考,不做過多說明.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9399028/viewspace-686557/，如需轉載，請註明出處，否則將追究法律責任。