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

fzymr 2008-05-27 10:16:13

大表迁移的问题：
由于上新系统需要将原系统的数据导入到新系统的表。原系统的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, 如何优化程序?

谢谢各位高手指正!

...全文