实验要求：
1.以常用“名字大全”与“百家姓”数据集为基础，生成不小于1千万条stud记录，要求，姓名的重复率不超过10%，学号以ABCD16EFGH为格式模板，即其中16是固定的，AB为从01到80，CD为从01到90，EF为01到50，GH为01到32；性别中，男、女占比为99%到99.5%。TEL与E-mail不作要求，但不能全空。Birthday要求从‘19940101’到‘19990731’分布。要求记录ORACLE数据文件的大小变化。（需要编制过程）
imp U_J122/U_J123 file = E:\大三上\大型数据库\大型数据库\name.dmp fromuser = yjs touser = U_J122 commit=ydrop table name1;create table name1(word1 NCHAR(1));drop table name2;create table name2(word2 NCHAR(1));drop table name3;create table name3(word3 NCHAR(1));insert into name1 select distinct substr(name,1,1) from name;insert into name2 select distinct substr(name,2,1) from name;insert into name3 select distinct substr(name,3,1) from name WHERE ROWNUM <=12;create table T_namex_j122(namex VARCHAR2(10));insert into T_namex_j122 (select word1||word2||word3 nameitem from name_1st,name_2nd,name_3rd);drop table T_studnew_j122;create table T_studnew_j122( sno CHAR(10) primary key, sname VARCHAR2(10), sex VARCHAR2(4), tel VARCHAR2(15), email VARCHAR2(30), birthday DATE);set TIMING on;drop procedure P_create_stud_j122;create OR REPLACE PROCEDURE P_create_stud_j122 IS cnt PLS_INTEGER:=1; msno CHAR(10); mname VARCHAR2(10); msex VARCHAR2(4); mtel VARCHAR2(15); memail VARCHAR2(30); mbirthday DATE; maxl PLS_INTEGER:=10322736; yy PLS_INTEGER; mm PLS_INTEGER; dd PLS_INTEGER; rand_sex PLS_INTEGER; rand_eml varchar2(30); CURSOR c_name IS (select namex from T_namex_j122);BEGIN OPEN c_name; FOR ab IN 1..80 LOOP EXIT WHEN cnt>maxl; FOR cd IN 1..90 LOOP EXIT WHEN cnt>maxl; FOR ef IN 1..50 LOOP EXIT WHEN cnt>maxl; FOR gh IN 1..32 LOOP EXIT WHEN cnt>maxl; msno:= TO_CHAR(ab,'FM09')||TO_CHAR(cd,'FM09')||'16'||TO_CHAR(ef,'FM09')||TO_CHAR(gh,'FM09'); FETCH c_name INTO mname; rand_sex:=DBMS_RANDOM.VALUE(1,100); IF rand_sex<=40 THEN msex:='男'; ELSIF rand_sex<=99 THEN msex:='女'; ELSE msex:='其他'; END IF; rand_eml:=DBMS_RANDOM.STRING('X',10); memail:=rand_eml||'@163.com'; mtel:=ab||cd||ef||gh; yy:=dbms_random.value(1994,1999); IF yy<1999 THEN mm:=dbms_random.value(1,12); ELSE mm:=dbms_random.value(1,7); END IF; IF mm=2 AND ((0=yy mod 4 AND 0!=yy mod 100) OR 0=yy mod 400)THEN dd:=dbms_random.value(1,29); ELSIF mm=2 THEN dd:=dbms_random.value(1,28); ELSIF mm=1 OR mm=3 OR mm=5 OR mm=7 OR mm=8 OR mm=10 OR mm=12 THEN dd:=dbms_random.value(1,31); ELSE dd:=dbms_random.value(1,30); END IF; mbirthday:=TO_DATE(yy||TO_CHAR(mm,'FM09')||TO_CHAR(dd,'FM09'),'yyyymmdd'); INSERT INTO T_studnew_j122(sno,sname,sex,email,tel,birthday) VALUES (msno,mname,msex,memail,mtel,mbirthday); cnt:=cnt+1; END LOOP; END LOOP; END LOOP; END LOOP;CLOSE c_name;END;./exec P_create_stud_j122; 2．分别测试stud有主键与没有主键情形下生成记录的时间。
3．建立基于name的索引index_name，测试建立的时间与建立索引前后查询某一姓名及某一姓的时间长度。
4．测试索引index_name建立前后，分姓（简单地理解为姓名的第1，2位）的记录数统计时间。
select * from T_studnew_j122 where sno='0101160101';select * from T_studnew_j122 where sname='任盈强';select * from T_studnew_j122 where sname like '任%';create index index_name on T_studnew_j122(sname);select * from T_studnew_j122 where sname='任盈强';select * from T_studnew_j122 where sname like '任%';
5．按学号首位建立10个分区分别为part_0到part_9，测试建立分区前后分首位统计人数与分专业（EF位）统计人数的时间差别。
DROP TABLE T_studnew_j122;CREATE TABLE T_studnew_j122( sno CHAR(10), sname VARCHAR2(10), sex VARCHAR2(4), tel VARCHAR2(15), email VARCHAR2(30), birthday DATE)partition by range(sno)(partition part_0 values less than ('1000000000'),partition part_1 values less than ('2000000000'),partition part_2 values less than ('3000000000'),partition part_3 values less than ('4000000000'),partition part_4 values less than ('5000000000'),partition part_5 values less than ('6000000000'),partition part_6 values less than ('7000000000'),partition part_7 values less than ('8000000000'));select count(*) from T_studnew_j122 partition(part_0);select count(*) from T_studnew_j122 partition(part_1);select count(*) from T_studnew_j122 partition(part_2);select count(*) from T_studnew_j122 partition(part_3);select count(*) from T_studnew_j122 partition(part_4);select count(*) from T_studnew_j122 partition(part_5);select count(*) from T_studnew_j122 partition(part_6);select count(*) from T_studnew_j122 partition(part_7);