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