一次和几个同事要做一个test,需要数据量在6000万条数据左右,先用存储过程生成数据结果在开始时候还可以,后来几乎1分钟几分钟一条记录,而且最重要的是存储过程中忘记写commit;结果数据达到1G后回滚段溢出,数据库崩溃,重新启动后还是老样子到后来很慢。
后来采用insert select的方法,确实很快。
相关方法如下:
1。
spool task1.log
create table cas_smartcard
(
ic_no char(10) ,
ic_status number(1),
stock_date date,
used_date date,
group_id number(6),
USER_INDEX number(3),
area_id number(5),
ic_id varchar2(12),
primary key(ic_no)
)storage (initial 120M next 12M pctincrease 0);
--create by runnerunning@163.com
alter table cas_smartcard disable primary key;
truncate table cas_smartcard;
Exec Init_data_IC('20040601',1,0,300031);
Exec Init_data_IC('20040701',2,1172,300031);
Exec Init_data_IC('20040801',3,2344,300031);
Exec Init_data_IC('20040901',4,3516,300031);
Exec Init_data_IC('20041001',5,4688,300031);
Exec Init_data_IC('20041101',6,5860,300031);
Exec Init_data_IC('20041201',7,7032,300031);
Exec Init_data_IC('20050101',10,8204,300031);
Exec Init_data_IC('20050201',100,9376,300031);
Exec Init_data_IC('20040821',1,10548,299711);
--create by runnerunning@163.com
create index idx_smartcard_ic_no on cas_smartcard(ic_no)
tablespace indx
storage (initial 40M next 4M pctincrease 0 );
spool off;
2。
spool task2.log
drop table cas_update_auth;
--create by runnerunning@163.com
create table cas_update_auth parallel as
select ic_no ic_no,0 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,1 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,2 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,3 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,4 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,5 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,6 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,7 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,8 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,9 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,10 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,11 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,12 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,13 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,14 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,15 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,16 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,17 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,18 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard
union all
select ic_no ic_no,19 object_id,stock_date auth_start,stock_date+60 auth_end,
1 auth_oper,sysdate oper_date,1 object_type,1 pre_auth
From cas_smartcard;
create index idx_update_auth on cas_update_auth(ic_no,object_id)
tablespace indx
storage (initial 400M next 40M pctincrease 0 );
drop table cas_auth;
--create by runnerunning@163.com
create table cas_auth parallel as
select ic_no, object_id ,
auth_start , auth_end ,
oper_date , object_type,
pre_auth
from cas_update_auth;
create index idx_auth on cas_auth(ic_no,object_id)
tablespace indx
storage (initial 400M next 40M pctincrease 0 );
spool off;
生成数据并导入CA数据
任务:1.生成CAS_SMARTCARD表(共300万记录)
2.生成CAS_AUTH与CAS_UPDATE_AUTH表(各6000万记录)
--create by runnerunning@163.com
解决办法:
一.使用过程生成CAS_SMARTCARD表记录,再使用过程生成CAS_AUTH与CAS_UPDATE_AUTH表记录
前提条件:无
速度:慢,经测试4分钟生成20万条记录,生成一个大表需20小时
--create by runnerunning@163.com
二.使用过程生成CAS_SMARTCARD表,再通过创建CAS_AUTH与CAS_UPDATE_AUTH表(同时生成记录)
前提条件:两个大表与CAS_SMARTCARD表有关联
速度:快,经测试4分钟生成5100万条记录,生成一个大表需15分钟
不足:容易产生空间碎片
具体执行:
1.创建CAS_SMARTCARD空表,并运行过程生成CAS_SMARTCARD表记录。见脚本task1.sql;
2.创建CAS_UPDATE_AUTH表同时生成记录.并创建索引。见脚本task2.sql;
3.创建CAS_AUTH表同时生成记录.并创建索引。见上脚本task2.sql;
注意事项:
1.创建大表的索引,需要大TEMP
2.生成大批量数据时,要注意提交记录。同时可以设置为不进行日志记录。
3.注意计算数据存储空间。如CAS_SMARTCARD表需156M,CAS_UPDATE_AUTH表需3.2G.
生成CAS_UPDATE_AUTH表的索引,占用索引空间需1.52G,临时空间需1.6G
--create by runnerunning@163.com