请教大表迁移的问题，谢谢！

大表迁移的问题：
    由于上新系统需要将原系统的数据导入到新系统的表。原系统的10张左右的表对应新系统的一张表（简称 大表），在数据转换时需要判断原表之间是否有重复数据（表内数据和表与表之间都有重复的情况），还要做一些较复杂的逻辑判断,不仅是insert,还要update 数据。大概的数据有近2000万行。
    我现在的转换方法如下：
    1，将大表分组.为了避免对 大表写入太过频繁现将大表分成5张表，分别为大表1,大表2,大表3,大表4,大表5。按照不同的条件将数据分组，分别写入到5张表.
    2，分批提交. 使用动态游标的形式分批查询出数据,每批数据进行提交. (大概20多万行提交一次)
    3，使用批量绑定变量的方法: 使用forall 绑定变量更新数据.这样减少io开销.
    
    大体程序结构如下:
    
create or replace procedure p_test_forall_new(po_fhz out varchar2,po_msg out varchar2)
  is    TYPE t_lx IS TABLE OF varchar2(2) INDEX BY  BINARY_INTEGER;
    v_lx  t_lx;    TYPE table_大表 IS TABLE OF 大表%rowtype INDEX BY BINARY_INTEGER;
    v_大表 table_大表;    TYPE t_rowid IS TABLE OF varchar2(100) INDEX BY  BINARY_INTEGER;
    v_rowid  t_rowid;
    v_rowid_bj_tmp t_rowid;
    v_rowid_bj t_rowid;    TYPE t_dyn_cur is ref cursor;    cur_new t_dyn_cur;  begin    v_count := 0;
    po_fhz := '1';    v_lx(1) := '20';
    v_lx(2) := '20';
    v_lx(3) := '30';
    v_lx(4) := '40';
    v_lx(5) := '50';
    v_lx(6) := '53';    for i in 1..v_lx.count loop      for mod_cnt in 0..5 loop        v_大表.delete;
        v_rowid.delete;
        v_rowid_bj_tmp.delete;
        v_rowid_bj.delete;        v_j := 0;
        v_k := 0;        v_sql := 'SELECT /*+parallel(a 2)*/ 字段1,字段2,字段3...字段23'
                         ' rowid row_id,'||
                         'decode( nx,''2'', (select t.rowid from tmp_new_1 t  where t.grbh = a.grbh  and t.lx = a.lx  and t.bz = ''1'' and t.rq = a.rq  and t.rq = a.rq and t.dwbh = a.dwbh and t.bcnx = a.bcnx ) ,null ) rowid_bj'||
                         ' from tmp_new a '||
                         ' where lx = :x1'||
                         ' and mod( to_number(grbh) ,12 ) = :x2';
                         
        OPEN cur_new FOR v_sql using v_lx(i),mod_cnt;       LOOP
         v_j := v_j + 1;         FETCH cur_new INTO v_大表(v_j).字段1,..v_大表(v_j).字段23,v_rowid(v_j),v_rowid_bj_tmp(v_j);         IF cur_new%NOTFOUND Then
           v_大表.delete(v_j);
           v_rowid.delete(v_j);
           v_rowid_bj_tmp.delete(v_j);
           v_j := v_j - 1;
           EXIT;
         End IF;         --f_get_gryj_dup用到了索引,速度很快
         if f_get_gryj_dup( v_大表(v_j).grsxh,v_大表(v_j).xzlx,v_大表(v_j).jfny) = 1 then
           v_大表(v_j).yjlx := '3';
         end if;         if v_rowid_bj_tmp(v_j) is not null then
           v_k := v_k + 1;
           v_rowid_bj(v_k) := v_rowid_bj_tmp(v_j);
         end if;
      END LOOP;      CLOSE cur_new;      --保存数据
      if v_大表.count > 0 then        if v_大表(1).xzlx = '10' then
          FORALL v_j IN v_大表.first..v_大表.last
            insert into 大表_10 values v_大表(v_j);
               commit;
        end if;        if v_大表(1).xzlx = '20' then
          FORALL v_j IN v_大表.first..v_大表.last
            insert into 大表_20 values v_大表(v_j);
               commit;
        end if;        if v_大表(1).xzlx = '30' then
          FORALL v_j IN v_大表.first..v_大表.last
            insert into 大表_30 values v_大表(v_j);
               commit;
        end if;        if v_大表(1).xzlx = '40' then
          FORALL v_j IN v_大表.first..v_大表.last
            insert into 大表_40 values v_大表(v_j);
               commit;
        end if;        if v_大表(1).xzlx = '50' then
          FORALL v_j IN v_大表.first..v_大表.last
            insert into 大表_50 values v_大表(v_j);
               commit;
        end if;        if v_大表(1).xzlx = '53' then
          FORALL v_j IN v_大表.first..v_大表.last
            insert into 大表_50 values v_大表(v_j);
               commit;
        end if;      end if;      --如果已经转换了,标识为1
      if v_rowid.count > 0 then
         FORALL v_j IN v_rowid.first..v_rowid.last
           update TMP_new set sybz = '1' where rowid = v_rowid(v_j);
             commit;
      end if;      --如果已经用到关联表,将关联表的使用标志设置为1
      if v_rowid_bj.count > 1 then
         FORALL v_k IN v_rowid_bj.first..v_rowid_bj.last
           update tmp_new_1 set sybz = '1' where rowid = v_rowid_bj(v_k);
             commit;
 
      end if;    END LOOP;  end loop;  v_大表.delete;
  v_rowid.delete;
  v_rowid_bj_tmp.delete;
  v_rowid_bj.delete;  exception
    when others then
      v_大表.delete;
      v_rowid.delete;
      v_rowid_bj_tmp.delete;
      v_rowid_bj.delete;      
      po_msg := sqlerrm;
      return;
  end ;
  
现有如下问题:(测试环境 winxp + ora9.2.0.1.0 cpuT2370(1.73GHz),内存1G)
1, 速度慢 (经debug,分批提交一次大概需要30分钟左右)
2, 如何看运行时内存的使用情况? 因为用到了索引表,我的理解是索引表的数据放在pga中(不知道是否这样),通过
   select a.*, round(a.VALUE/1024/1024,2) "size(M)" from v$pgastat a; 
   查询发现
    total PGA inuse,total PGA allocated,maximum PGA allocated变化很快,是否应该就是本次程序运行内存变化的反应?但是提交后,清空索引表的数据,这三项指标数据都没有变化.等到再次循环一段时间后还会增加.
   我的理解是oracle在运行结束后才会释放索引表和游标占用的内存,在运行过程中即使关闭游标,清空索引表其内存还在占用?如果是这样如何才能查本次运行时内存的变化情况呢? 最好能查到游标,索引表分别占用内存的情况.
3, 如何优化程序? 谢谢各位高手指正!    
      
  

解决方案 »

  1.   

    可以考虑“梯形插入”，分别将10个表的数据对应插入到临时大表中，其余字段留空，再用临时大表的主键作GROUP BY操作合并数据，最后再INSERT到原大表中。
      

  2.   

    谢谢 Adrianlynn 程序会有较多的逻辑判断，所以一次性放入到临时大表可能不行。（本来打算用bulk into取数据的，由于中间的逻辑判断导致不能使用这种方式）