迅速插入大量測試資料的方法

要求：
使用最短的時間插入滿足如下條件的測試資料，15分鐘能完成嗎？
*1億條
*資料分佈滿足業務特徵
--序列唯一
--證件號碼唯一
--狀態0，80%；1，10%，2，5%，3，5%
--商戶號取自商戶表，其中前3個商戶資料佔比分別為30%,30%,20%，其餘商戶資料佔比平均分配

步驟：
1，待插入表準備
1.1,建立空表
create table trans_test(trans_id int,status varchar2(1),id_num varchar2(30),merchant_id int) nologging;

1.2,如果要在已有表中插入，刪除外來鍵、索引
比如：
dropr index idx_trans_test_id;

alter table trans_test drop constraint fk_trans_test_r_merchant;

2，準備資料
2.1建立商戶臨時表,資料分佈符合要求比例
create table merchant_test(id int,merchant_id int);

insert into merchant_test
select rownum rn,0 from dual connect by level<=100;
commit;

update merchant_test set merchant_id=1
where id between 1 and 30;

update merchant_test set merchant_id=2
where id between 31 and 60;

update merchant_test set merchant_id=3
where id between 61 and 80;

update merchant_test set merchant_id=id
where id between 81 and 100;
commit;

create index idx_merchant_test on merchant_test(id,merchant_id);

2.2表函式返回指定行數,可以返回指定行數
*說明:也可以用select rownum from dual connect by level<1e7 代替,大概需要300MByte記憶體

CREATE OR REPLACE PACKAGE f_n_rows IS
  TYPE outrec_typ IS RECORD(
    a INT);
  TYPE outrecset IS TABLE OF outrec_typ;
  FUNCTION get_n_rows(p INT) RETURN outrecset
    PARALLEL_ENABLE
    PIPELINED;
END f_n_rows;
/

CREATE OR REPLACE PACKAGE BODY f_n_rows IS
  FUNCTION get_n_rows(p INT) RETURN outrecset
    PARALLEL_ENABLE
    PIPELINED IS
    out_rec outrec_typ;
  BEGIN
    FOR x IN 1 .. p LOOP
      out_rec.a := x;
      PIPE ROW(out_rec);
    END LOOP;
    RETURN;
  END;
END f_n_rows;
/

select * from table(f_n_rows.get_n_rows(10));

2.3建立返回狀態函式,資料分佈符合要求
CREATE OR REPLACE FUNCTION get_status RETURN VARCHAR2
  PARALLEL_ENABLE IS
  l_rand INT;
BEGIN
  l_rand := dbms_random.value;
  RETURN(CASE WHEN l_rand <= 0.80 THEN '0' WHEN l_rand <= 0.90 THEN '1' WHEN
         l_rand <= 0.95 THEN '2' ELSE '3' END);
END;
/

2.4建立臨時sequence
drop sequence seq_trans_test;
create sequence seq_trans_test;

2.5建立種子表(1000萬資料),資料分佈符合要求
create table trans_test0 nologging parallel 8
as
select * from trans_test t;

--插入資料到種子表
--由於使用seq,無法使用並行
set timing on
alter session enable parallel dml;
INSERT /*+ append */
INTO trans_test0 tt
  SELECT seq_trans_test.nextval,
         get_status,
         'ID' || lpad(seq_trans_test.currval, 10, '0'),
         m.merchant_id
    FROM TABLE(f_n_rows.get_n_rows(1e7)) t, merchant_test m
   WHERE MOD(t.a, 100) + 1 = m.id;
commit;
alter session disable parallel dml;
--400sec

2.6建立偏移量表
drop table trans_test_offset;
create table trans_test_offset
as
select rownum id
from dual
connect by level<=10;

說明,以上過程耗時約500秒

3，插入資料
3.1使用笛卡兒連線重複插入
alter session enable parallel dml;
INSERT /*+ parallel(tt 8) */
INTO trans_test tt
  SELECT /*+ parallel(t 8) parallel(ttos 8) */ trans_id + (ttos.id-1)*1e7,  --offset
         status,
         'ID' || lpad(to_number(substr(id_num,3))+ (ttos.id-1)*1e7, 10, '0'),  --offset
         merchant_id
    FROM trans_test0 t,trans_test_offset ttos;
commit;
alter session disable parallel dml;
--98sec

3.2恢復表屬性，收集統計資料
alter table trans_test logging noparallel;

begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'trans_test',degree => 8);
end;
/
--24sec

說明,以上過程耗時約120秒

4，建立索引、外來鍵
create index idx_trans_test_id on trans_test(trans_id)
parallel 8
nologging;

alter index idx_trans_test_id logging noparallel;

alter table trans_test
add constraint fk_trans_test_r_merchant
 foreign key(merchant_id) references merchant(id);

5，刪除測試物件
drop sequence seq_trans_test;
drop table trans_test;
drop table trans_test0;
drop table merchant_test;
drop function get_status;
drop package f_n_rows;