使用bulkCollect解決資料遷移問題

機器配置:IBM P690 6U12G
資料庫版本: Oracle 9.2.0.6

需求: 根據主外來鍵關係遷移tb1446表內資料至tb1446_copy

表tb1446內有20個Hash分割槽,根據分割槽結合bulk collect技術,一次提交25000條記錄(需要測試後得出一次提交的記錄數)，使得整個單表遷移過程耗時最短.

[@more@]

SQL> select count(*) from ecifupdate.tb1446;

COUNT(*)
----------
7087611

SQL> select count(*) from ecifupdate.tb1560;

COUNT(*)
----------
74735643

編寫bulk collect方式的儲存過程.
migration_tb1446_part_00
migration_tb1446_part_01
migration_tb1446_part_02
migration_tb1446_part_03
.
.
.
migration_tb1446_part_19

給個sample參考(待完善)
create or replace procedure migration_tb1446_part_00
is
type tb_bulk_type is table of tb1446_copy%rowtype;
ref_bulk tb_bulk_type;
Cursor c_bulk is
select a.*
from ecifupdate.tb1446 partition(part_00) a,
ecifupdate.tb1560 b
where a.arrangement_id = b.linkedarrangement;
begin
open c_bulk;

loop
fetch c_bulk bulk collect into ref_bulk limit 25000;
forall i in 1 .. ref_bulk.count
insert into tb1446_copy values ref_bulk(i);
exit when c_bulk%notfound;
end loop;
close c_bulk;
end;

單個程式執行一個hash分割槽的耗時是00:01:16.68
SQL> exec migration_tb1446_part_00;

PL/SQL procedure successfully completed.

Elapsed: 00:01:16.68

考慮資源配置(cpu,i/o)可以採用並行方式處理.
我這裡四個程式並行處理20個分割槽總共耗時7分17秒完成.

遷移後tb1446_copy表記錄數
SQL> select count(*) from tb1446_copy;

COUNT(*)
----------
2890208

這裡要說明一點:
a.arrangement_id = b.linkedarrangement必須是主外來鍵連線才能保證tb1446_copy表內沒有重複資料.