ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 批量更新总结

批量更新总结

原创 Linux操作系统 作者:xz43 时间:2011-01-31 15:15:43 0 删除 编辑

最近遇到一个这么一个业务:每晚根据另一个系统上报的数据(表 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执行.

这里仅仅提供给大家一个批量更新的参考,不做过多说明.

补充:根据上月状态,初始化本月的状态,涉及两张千万级的表,采用以下脚本,使用了 bulk collect into limit 获取方式和 forall 循环相结合,根据 rowid 更新记录,对系统正常使用不会造成太大的影响,执行了10分半钟。
declare
  cursor md_20113 is
    select /*+parallel(y1,10) parallel(y3,10)+*/
     y3.rowid
      from pub_md_20111 y1, pub_md_20113 y3
     where y1.student_id = y3.student_id
       and y1.state = y3.state
       and y1.state = 3;
  type md_rec is table of varchar2(20);
  row_id md_rec;
begin
  open md_20113;
  loop
    fetch md_20113 bulk collect
      into row_id limit 10000;
    forall i in row_id.first .. row_id.last execute immediate
                                'update pub_md_20113 set state = 0 where rowid=:rid'
                                using row_id(i)
      ;   
    commit;
    exit when md_20113%notfound;
  end loop;
  close md_20113;
end;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9399028/viewspace-686557/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle AWR速查
请登录后发表评论 登录
全部评论

注册时间:2010-11-16

  • 博文量
    420
  • 访问量
    1744219