--创建表create table TEST_STUDENTS( STUDENT_ID VARCHAR2(10), STUDENT_NAME VARCHAR2(100), AGE NUMBER(5), SEX NUMBER(2), CLASSID VARCHAR2(10))tablespace BLOG;-- Add comments to the table comment on table TEST_STUDENTS is '学生表';-- Add comments to the columns comment on column TEST_STUDENTS.STUDENT_ID is '学生id';comment on column TEST_STUDENTS.STUDENT_NAME is '学生姓名';comment on column TEST_STUDENTS.AGE is '学生年龄';comment on column TEST_STUDENTS.SEX is '学生性别';comment on column TEST_STUDENTS.CLASSID is '学生班级';-- Create/Recreate indexes create unique index IDX_STUDENT_ID_TEST on TEST_STUDENTS (STUDENT_ID) tablespace BLOG;
--删除表drop table TEST_STUDENTS;
--插入数据--=============================================================================================================/**1条并发
**/declare var_count number(20); -- 数据量： 20wvar_sequence number(20); -- 学生序列号begin var_count := 2000000; var_sequence := 1; EXECUTE IMMEDIATE 'alter table TEST_STUDENTS nologging'; for i in var_sequence .. var_count loop
insert into TEST_STUDENTS (STUDENT_ID,STUDENT_NAME,AGE,SEX,CLASSID)values(lpad(i,10,'0'),'网一@'||i,'20','1','0001'); if(mod(i,20000)=0)then commit; end if; end loop; commit; EXECUTE IMMEDIATE 'alter table TEST_STUDENTS logging';end;/
--删除数据truncate table TEST_STUDENTS;
--查询数据select * from TEST_STUDENTSselect count(*) from TEST_STUDENTS--=============================================================================================================/**10条并发
**/--插入数据declare var_count number(20); -- 数据量： 20wvar_sequence number(20); -- 学生序列号j number(20);begin var_count := 200000; -- 数据量计算 var_count * 10 var_sequence := 1; j := 0; EXECUTE IMMEDIATE 'alter table TEST_STUDENTS nologging'; for i in var_sequence .. var_count loop
--insert into TEST_STUDENTS (STUDENT_ID,STUDENT_NAME,AGE,SEX,CLASSID)values(lpad(i,10,'0'),'网一@'||i,'20','1','0001'); insert into TEST_STUDENTS ( select lpad(j+1,10,'0'),'网一@'||(j+1),'20','1','0001' from dual union select lpad(j+2,10,'0'),'网一@'||(j+2),'20','1','0001' from dual union select lpad(j+3,10,'0'),'网一@'||(j+3),'20','1','0001' from dual union select lpad(j+4,10,'0'),'网一@'||(j+4),'20','1','0001' from dual union select lpad(j+5,10,'0'),'网一@'||(j+5),'20','1','0001' from dual union select lpad(j+6,10,'0'),'网一@'||(j+6),'20','1','0001' from dual union select lpad(j+7,10,'0'),'网一@'||(j+7),'20','1','0001' from dual union select lpad(j+8,10,'0'),'网一@'||(j+8),'20','1','0001' from dual union select lpad(j+9,10,'0'),'网一@'||(j+9),'20','1','0001' from dual union select lpad(j+10,10,'0'),'网一@'||(j+10),'20','1','0001' from dual ); j :=j+10; if(mod(i,2000)=0)then commit; end if; end loop; commit; EXECUTE IMMEDIATE 'alter table TEST_STUDENTS logging';end;/
--=============================================================================================================/**1w条并发
**/
/** 创建批量表 sk_load */ create table sk_load(id1 NUMBER(10));
declarevar_bingfa_count number(20); --批量并发插入大小var_count number(20); --数据量j number(20); --递增序列数k number(20); --一次提交数据大小
begin /** 变量赋值 */ var_bingfa_count := 10000; -- 1w条并发 var_count := 200; -- 数据量计算 var_count * var_bingfa_count = 200 * 1w = 200 0000 j := 0; -- 递增初始化为0 k := 4; -- 一次提交数commit = k * var_bingfa_count = 2 * 1w = 20000
/** insert 批量表 */ for i in 1 .. var_bingfa_count loop insert into sk_load (id1)values(i); commit; end loop;
/** 批量 insert 数据 */ EXECUTE IMMEDIATE 'alter table TEST_STUDENTS nologging'; for i in 1 .. var_count loop insert into TEST_STUDENTS select lpad(j+id1,10,'0'),'网一@'||(j+id1),'20','1','0001' from sk_load ; --j=j+并发数 j :=j+var_bingfa_count; --提交数 = 2 * 并发数 if(mod(i,k)=0)then commit; end if; end loop; EXECUTE IMMEDIATE 'alter table TEST_STUDENTS logging';
end;/
/** 删除批量表 sk_load */ drop table sk_load;
--============================================================================================================= /** 结论 */ --花费时间数据量： 20000001条并发 84s 2w提交10条并发：24s 2w提交1w条并发：23s 2w提交
