由于公司项目上一些事情,我想测试一下1亿条记录的表,到底有多大。
1.创建表
-
create table smmaidy(data_sj number,rcd number,seq number,mode_ms varchar(16),mainacctid varchar2(256),
-
loginname varchar2(64),username varchar2(32),valid char(1),lockstatus char(1),accttype varchar2(32),
-
rolelist varchar2(500),effecttime timestamp,expiretime timestamp,establishtime timestamp,areaid varchar2(10),
-
orgid varchar2(10),orgname varchar2(500),updatetime timestamp,flag char(1),orderid varchar2(64)
-
);
2.建个序列
这个最好用plsql鼠标点点创建,方便。
-
create sequence SEQ_SCAP
-
minvalue 0
-
maxvalue 999999999999999999999
-
start with 4634201
-
increment by 1
-
cache 20;
3.创建存储过程循环插入
好久不写了,之前写的代码也都丢失了,居然没保存回来,看来博客这东西,还是有点用的,所以顺便也从百度贴回来了以前曾经写的几个小程序,忘记语法什么的,最好看PLSQL_primary.pdf这个文件。
-
create or replace procedure pro_smmai(nu in number) is
-
--i number:=1;
-
begin
-
for i in 1..nu loop
-
insert into smmaidy values(1,1,seq_scap.nextval,'模式','mainacctid-dfafadfaffadfafafafa',
-
'xichubawang','kaige','1','1','32fdafdafadfafadfafa','文件头格式参见两级接口规则的5.2.4节,此处只定义数据记录部分的格式',
-
sysdate,sysdate,sysdate,'10fdafa','10fdadf','组织全路径名称。即从根节点到当前节点的完整路径,节点用“-”分隔。
-
举例:某移动-某地市分公司-某县市分公司-某营业厅',sysdate,'0','工单/公文号');
-
end loop;
-
commit;
-
end pro_smmai;
4.测试
先插1w条数据试试,由于我之前建表之后测试,插了两条,所以是10002。如果拼写错误,记得看命令行提示,我看到之后,居然去检查程序去了。。。
-
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
-
Connected as yk@GLOBALYK
-
-
SQL> set serveroutput on size 9999;
-
SQL> set time on;
-
20:28:52 SQL> set timing on;
-
20:29:06 SQL> select sysdate from dual;
-
SYSDATE
-
-----------
-
2015-3-30 2
-
Executed in 0 seconds
-
-
20:29:13 SQL> set linesize 9999;
-
20:29:58 SQL> exec pro_smmail(10000);
-
begin pro_smmail(10000); end;
-
ORA-06550: 第 1 行, 第 7 列:
-
PLS-00201: 必须声明标识符 'PRO_SMMAIL'
-
ORA-06550: 第 1 行, 第 7 列:
-
PL/SQL: Statement ignored
-
-
20:31:13 SQL> exec pro_smmai(10000);
-
PL/SQL procedure successfully completed
-
Executed in 1.25 seconds
-
-
20:32:09 SQL> select count(*) from smmaidy;
-
COUNT(*)
-
----------
-
10002
-
Executed in 0.047 seconds
-
-
20:32:47 SQL> delete from smmaidy;
-
10002 rows deleted
-
Executed in 0.109 seconds
-
-
20:33:11 SQL> commit;
-
Commit complete
-
Executed in 0 seconds
-
-
20:33:17 SQL>
我执行了
测试1亿条,结果执行了1个多小时都没完,无法强制结束,强制关闭数据库,关闭后也异常了,更别提执行的过程中再去执行查询了。。
后来我就测试100w条的效果:
-
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
-
Connected as yk@GLOBALYK
-
-
SQL> set serveroutput on;
-
SQL> set timing on;
-
SQL> exec pro_smmai(1000000);
-
PL/SQL procedure successfully completed
-
Executed in 83.704 seconds
-
-
SQL> select count(*) from smmaidy;
-
COUNT(*)
-
----------
-
1000000
-
Executed in 7.015 seconds
-
-
SQL>
-
表的大小是:
-
SQL> select bytes/1024/1024 m from dba_segments where segment_name='SMMAIDY';
M
----------
496
SQL>
-
建个索引看看效率:
SQL> select count(seq) from smmaidy;
COUNT(SEQ)
----------
1000000
Executed in 0.234 seconds
SQL>
我这个双核的电脑,看来跑百万条记录、没索引的情况下,还凑合吧。
阅读(8663) | 评论(1) | 转发(1) |