badEgg > 数据库实验 - oracle

数据库实验 - oracle

2019-12-09

数据库

数据库实验三

吐血= =过几天生日家里要来人，还有这么多实验作业，惹不起惹不起

实验三要求:

1.以常用“名字大全”与“百家姓”数据集为基础，生成不小于1千万条stud记录，要求，姓名的重复率不超过10%，学号以ABCD17EFGH为格式模板，即其中17是固定的，AB为从01到80，CD为从01到90，EF为01到50，GH为01到32；性别中，男、女占比为99%到99.5%。TEL与E-mail不作要求，但不能全空。Birthday要求从‘19940101’到‘19990731’分布。要求记录ORACLE数据文件的大小变化。（需要编制过程）

2．分别测试stud有主键与没有主键情形下生成记录的时间。

3．建立基于sname的索引index_name，测试建立的时间与建立索引前后查询某一姓名及某一姓的时间长度。

4．测试索引index_name建立前后，分姓（简单地理解为姓名的第1，2位）的记录数统计时间。

5．按学号首位建立10个分区分别为part_0到part_9，测试建立分区前后分首位统计人数与分专业（EF位）统计人数的时间差别。

要求一需要1千万以上的stud记录并有各种各样的要求，那么就需要先编造这一千万条数据，上次做的时候用了常用姓 X 常用字 X 常用字的笛卡儿积，那么就出了一个问题，常用字的数据集合是3000，那么名部分的 3000 X 3000 = 0.9kw，我们一共需要1kw数据，而第一个名字就占了0.9kw，这样明显不太行，所以我们需要另一个合理的编造数据方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
--先建立学生表用于导入数据
drop table student_J314;
create table student_J314(
    sno char(10) primary key,
    name varchar(10),
    sex varchar(2),
    tel varchar(20),
    e_mail varchar(20),
    birthday date
);
set TIMING on;                             --康康等下的运行花了多少时间

--导入数据
drop table first_name;
create table first_name(
    fname varchar(5)
);
drop table second_name;
create table second_name(
	sname varchar(5)
);
drop table name;
create table name(
	name varchar(10)
);
--由于数据源在excel里，我们需要先导出来，求不出错一个一个来
--first_name
load data
infile 'd:\blog\first.csv'
append into table first_name fields terminated by ','
trailing nullcols(fname)
> sqlldr userid = U_J314/YcYyj1234 control = d:\blog\load.ctl
--415条记录
--second_name
load data
infile 'd:\blog\second.csv'
append into table second_name fields terminated by ','
trailing nullcols(sname)
> sqlldr userid = U_J314/YcYyj1234 control = d:\blog\second.ctl
--3177条记录
--姓和名都有了现在就需要来随机组合成名字了（编数据都要编得这么麻烦，没法吐槽了）
--我创建一个生成随机名字的过程，为了防止出现某个姓的名字过多，这次我就用111 * 111 * 999的方法生成不少于一千万条数据(实际上我是生成了11111111条数据)
drop procedure P_name_J314;
create or replace procedure P_name_J314
is
	cnt PLS_INTEGER:=1;
	first varchar(5);
	second varchar(5);
	third varchar(5);
	maxl PLS_INTEGER:=11111111;
begin
	FOR a in (select * from first_name where rownum<=100)
		loop exit when cnt > maxl;
		first:=a.fname;
		FOR b in (select * from second_name where rownum<=101)
			loop exit when cnt > maxl;
			second:=b.sname;
			FOR c in (select * from second_name where rownum<=1001)
				loop exit when cnt > maxl;
				third := c.sname;
				insert into name values (first||second||third);
				cnt := cnt + 1;
			END LOOP;
		END LOOP;
	END LOOP;
END;
.
/



--现在我们来建立插入数据的过程
drop procedure P_student_J314;
create or replace procedure P_student_J314
is
	cnt PLS_INTEGER:=1;
	msno varchar(10);
	mname varchar(10);
	msex varchar(2);
	mtel varchar(20);
	me_mail varchar(20);
	mbirthday date;
	
	rsex PLS_INTEGER;
	rdate PLS_INTEGER;
	maxl PLS_INTEGER:=10001111;
	dif PLS_INTEGER;
	
	cursor c_name is (select name from name);
begin
	dif := ROUND(TO_NUMBER(to_date('19990731', 'YYYYMMDD') - to_date('19940101', 'YYYYMMDD')));
	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;
					
					FETCH c_name INTO mname;
					rsex := DBMS_RANDOM.VALUE(1,1000);   --随机性别，并把比例分好
					if rsex <= 496
						then msex := '男';
					elsif rsex <= 993
						then msex := '女';
					else
						msex := '无';
					end if;
					
					rdate := DBMS_RANDOM.VALUE(0,dif);    --通过时间差来随机日期
					mbirthday := to_date('19940101', 'YYYYMMDD') + rdate;
					
					insert into student_J314 values(TO_CHAR(ab,'FM09')||TO_CHAR(cd,'FM09')||'17'||TO_CHAR(ef,'FM09')||TO_CHAR(gh,'FM09'), mname, msex, '123456', '321654', mbirthday);
					
					cnt := cnt + 1;
					
				END LOOP;
			END LOOP;
		END LOOP;
	END LOOP;
END;
.
/

--然后，我们先查看数据文件的大小，这一句是查看数据文件路径
select file_name , tablespace_name from dba_data_files;

--下一步就是插入
exec P_student_J314;

--时间空间变化如下

2 要求有主键与没主键生成记录的时间比较

那就是再插入一次没主键的咯，有主键要判断主键约束，所以按道理来说没主键会快一些

下面是结果，果然是没主键快一些

3 要求建立基于sname的索引，并比较建立前后

既然要比较建立索引的前后，所以我先把建立前的时间长度做好

1
2
3
4
5
6
7
8
9
10
select * from student_J314 where sno='0302170101';       --可以看到这个人叫云夫步
select count(*) from student_J314 where name='云夫步';          
select count(*) from student_J314 where name like '云%';
select count(*) from student_J314 where name like '云夫%';

create index index_name on student_J314(name);
select count(*) from student_J314 where name='云夫步';          
select count(*) from student_J314 where name like '云%';
select count(*) from student_J314 where name like '云夫%';
--运行过程及时间如下，显然建立索引后速度快得多

4 分区0-9，分首位统计与分专业统计的差别

完蛋，现在要分区，就要把之前建好的表删掉重建，早知道就把之前的数据分别存好了

那么就从删表与重建开始

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
delete from student_J314;
drop table student_J314;
create table student_J314(
    sno char(10) primary key,
    name varchar(10),
    sex varchar(2),
    tel varchar(20),
    e_mail varchar(20),
    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'),
	partition part_8 values less than ('9000000000'),
	partition part_9 values less than ('9999999999')
);
exec P_student_J314;
select count(*) from student_J314 partition(part_3) where sno like '3%';
select count(*) from student_J314 where sno like '______01%';