迅速插入大量测试数据的方法

要求：
使用最短的时间插入满足如下条件的测试数据，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;

 

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

转载于:http://blog.itpub.net/18922393/viewspace-714840/