用分区表?我自己做了些测试数据,2个主要的表:
people表结构:数据量2000W,SELF_DESC和SELF_DESC_LONG内容一致,自我介绍,就是想看一下不同长度的varchar2查询时是否有影响。
Name Null? Type
----------------------------------------- -------- ----------------------------
P_NO NOT NULL NUMBER --流水号,主键
P_ID VARCHAR2(18) --身份证号
SEX NUMBER(2) --性别,1:男,2:女
BIRTH_DATE DATE
SELF_DESC VARCHAR2(50)
SELF_DESC_LONG VARCHAR2(300)
CITY_ID NUMBER --城市id,外键SC表结构,是存储人的课程成绩的,cid是课程号,pno是people的p_no,外键,数据量6000W,我让它生成每个人随机选3门课,课程号1---10.PNO和CID是联合主键
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER
CID NOT NULL NUMBER
GRADE NUMBER下面分别是两个表的样例数据:
P_NO P_ID SEX BIRTH_DATE SELF_DESC SELF_DESC_LONG CITY_ID
1 5100137 210211197805100137 2 1981/08/06 hi,this is description 5100137 hi,this is description 5100137 8
2 5100138 210211197805100138 1 1964/10/13 hi,this is description 5100138 hi,this is description 5100138 4
3 5100139 210211197805100139 2 2000/12/05 hi,this is description 5100139 hi,this is description 5100139 4
4 5100140 210211197805100140 1 2008/09/02 hi,this is description 5100140 hi,this is description 5100140 7
5 5100141 210211197805100141 2 1993/07/08 hi,this is description 5100141 hi,this is description 5100141 2
6 5100142 210211197805100142 1 1957/12/14 hi,this is description 5100142 hi,this is description 5100142 3
7 5100143 210211197805100143 2 1965/02/16 hi,this is description 5100143 hi,this is description 5100143 3
8 5100144 210211197805100144 1 1972/06/15 hi,this is description 5100144 hi,this is description 5100144 2
9 5100145 210211197805100145 2 1998/05/14 hi,this is description 5100145 hi,this is description 5100145 3
10 5100146 210211197805100146 1 1997/02/05 hi,this is description 5100146 hi,this is description 5100146 9
11 5100147 210211197805100147 2 1984/06/30 hi,this is description 5100147 hi,this is description 5100147 2
12 5100148 210211197805100148 1 2001/04/05 hi,this is description 5100148 hi,this is description 5100148 8
13 5100149 210211197805100149 2 1959/06/02 hi,this is description 5100149 hi,this is description 5100149 5
14 5100150 210211197805100150 1 1983/04/26 hi,this is description 5100150 hi,this is description 5100150 3
15 5100151 210211197805100151 2 1993/10/06 hi,this is description 5100151 hi,this is description 5100151 3
16 5100152 210211197805100152 1 1928/10/10 hi,this is description 5100152 hi,this is description 5100152 2
17 5100153 210211197805100153 2 1978/03/24 hi,this is description 5100153 hi,this is description 5100153 10
18 5100154 210211197805100154 1 1978/12/13 hi,this is description 5100154 hi,this is description 5100154 3
19 5100155 210211197805100155 2 1972/05/27 hi,this is description 5100155 hi,this is description 5100155 4
20 5100156 210211197805100156 1 2004/03/24 hi,this is description 5100156 hi,this is description 5100156 6
21 5100157 210211197805100157 2 1976/11/26 hi,this is description 5100157 hi,this is description 5100157 6
PNO CID GRADE
1 11600094 6 92
2 11600095 8 72
3 11600095 4 88
4 11600095 6 94
5 11600096 1 72
6 11600096 6 30
7 11600096 4 77
8 11600097 8 73
9 11600097 7 69
10 11600097 9 39
11 11600098 2 89
12 11600098 9 53
13 11600098 5 82
14 11600099 2 72
15 11600099 10 46
16 11600099 5 98
17 11600100 8 95
18 11600100 5 76
19 11600100 1 58
20 11600101 5 70
21 11600101 1 48
people表结构:数据量2000W,SELF_DESC和SELF_DESC_LONG内容一致,自我介绍,就是想看一下不同长度的varchar2查询时是否有影响。
Name Null? Type
----------------------------------------- -------- ----------------------------
P_NO NOT NULL NUMBER --流水号,主键
P_ID VARCHAR2(18) --身份证号
SEX NUMBER(2) --性别,1:男,2:女
BIRTH_DATE DATE
SELF_DESC VARCHAR2(50)
SELF_DESC_LONG VARCHAR2(300)
CITY_ID NUMBER --城市id,外键SC表结构,是存储人的课程成绩的,cid是课程号,pno是people的p_no,外键,数据量6000W,我让它生成每个人随机选3门课,课程号1---10.PNO和CID是联合主键
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER
CID NOT NULL NUMBER
GRADE NUMBER下面分别是两个表的样例数据:
P_NO P_ID SEX BIRTH_DATE SELF_DESC SELF_DESC_LONG CITY_ID
1 5100137 210211197805100137 2 1981/08/06 hi,this is description 5100137 hi,this is description 5100137 8
2 5100138 210211197805100138 1 1964/10/13 hi,this is description 5100138 hi,this is description 5100138 4
3 5100139 210211197805100139 2 2000/12/05 hi,this is description 5100139 hi,this is description 5100139 4
4 5100140 210211197805100140 1 2008/09/02 hi,this is description 5100140 hi,this is description 5100140 7
5 5100141 210211197805100141 2 1993/07/08 hi,this is description 5100141 hi,this is description 5100141 2
6 5100142 210211197805100142 1 1957/12/14 hi,this is description 5100142 hi,this is description 5100142 3
7 5100143 210211197805100143 2 1965/02/16 hi,this is description 5100143 hi,this is description 5100143 3
8 5100144 210211197805100144 1 1972/06/15 hi,this is description 5100144 hi,this is description 5100144 2
9 5100145 210211197805100145 2 1998/05/14 hi,this is description 5100145 hi,this is description 5100145 3
10 5100146 210211197805100146 1 1997/02/05 hi,this is description 5100146 hi,this is description 5100146 9
11 5100147 210211197805100147 2 1984/06/30 hi,this is description 5100147 hi,this is description 5100147 2
12 5100148 210211197805100148 1 2001/04/05 hi,this is description 5100148 hi,this is description 5100148 8
13 5100149 210211197805100149 2 1959/06/02 hi,this is description 5100149 hi,this is description 5100149 5
14 5100150 210211197805100150 1 1983/04/26 hi,this is description 5100150 hi,this is description 5100150 3
15 5100151 210211197805100151 2 1993/10/06 hi,this is description 5100151 hi,this is description 5100151 3
16 5100152 210211197805100152 1 1928/10/10 hi,this is description 5100152 hi,this is description 5100152 2
17 5100153 210211197805100153 2 1978/03/24 hi,this is description 5100153 hi,this is description 5100153 10
18 5100154 210211197805100154 1 1978/12/13 hi,this is description 5100154 hi,this is description 5100154 3
19 5100155 210211197805100155 2 1972/05/27 hi,this is description 5100155 hi,this is description 5100155 4
20 5100156 210211197805100156 1 2004/03/24 hi,this is description 5100156 hi,this is description 5100156 6
21 5100157 210211197805100157 2 1976/11/26 hi,this is description 5100157 hi,this is description 5100157 6
PNO CID GRADE
1 11600094 6 92
2 11600095 8 72
3 11600095 4 88
4 11600095 6 94
5 11600096 1 72
6 11600096 6 30
7 11600096 4 77
8 11600097 8 73
9 11600097 7 69
10 11600097 9 39
11 11600098 2 89
12 11600098 9 53
13 11600098 5 82
14 11600099 2 72
15 11600099 10 46
16 11600099 5 98
17 11600100 8 95
18 11600100 5 76
19 11600100 1 58
20 11600101 5 70
21 11600101 1 48
PNO CID GRADE
11600094 6 92
11600095 8 72
11600095 4 88
11600095 6 94
11600096 1 72
11600096 6 30
11600096 4 77
11600097 8 73
11600097 7 69
11600097 9 39
11600098 2 89
11600098 9 53
11600098 5 82
11600099 2 72
11600099 10 46
11600099 5 98
11600100 8 95
11600100 5 76
11600100 1 58
11600101 5 70
11600101 1 48
主题中把序号也帖上去了,这排版。
P_NO P_ID SEX BIRTH_DATE SELF_DESC SELF_DESC_LONG CTY_ID
5100137 210211197805100137 2 1981/08/06 hi,this is description 5100137 hi,this is description 5100137 8
5100138 210211197805100138 1 1964/10/13 hi,this is description 5100138 hi,this is description 5100138 4
5100139 210211197805100139 2 2000/12/05 hi,this is description 5100139 hi,this is description 5100139 4
5100140 210211197805100140 1 2008/09/02 hi,this is description 5100140 hi,this is description 5100140 7
5100141 210211197805100141 2 1993/07/08 hi,this is description 5100141 hi,this is description 5100141 2
5100142 210211197805100142 1 1957/12/14 hi,this is description 5100142 hi,this is description 5100142 3
5100143 210211197805100143 2 1965/02/16 hi,this is description 5100143 hi,this is description 5100143 3
5100144 210211197805100144 1 1972/06/15 hi,this is description 5100144 hi,this is description 5100144 2
5100145 210211197805100145 2 1998/05/14 hi,this is description 5100145 hi,this is description 5100145 3
5100146 210211197805100146 1 1997/02/05 hi,this is description 5100146 hi,this is description 5100146 9
5100147 210211197805100147 2 1984/06/30 hi,this is description 5100147 hi,this is description 5100147 2
5100148 210211197805100148 1 2001/04/05 hi,this is description 5100148 hi,this is description 5100148 8
5100149 210211197805100149 2 1959/06/02 hi,this is description 5100149 hi,this is description 5100149 5
5100150 210211197805100150 1 1983/04/26 hi,this is description 5100150 hi,this is description 5100150 3
5100151 210211197805100151 2 1993/10/06 hi,this is description 5100151 hi,this is description 5100151 3
5100152 210211197805100152 1 1928/10/10 hi,this is description 5100152 hi,this is description 5100152 2
5100153 210211197805100153 2 1978/03/24 hi,this is description 5100153 hi,this is description 5100153 1
5100154 210211197805100154 1 1978/12/13 hi,this is description 5100154 hi,this is description 5100154 3
5100155 210211197805100155 2 1972/05/27 hi,this is description 5100155 hi,this is description 5100155 4
5100156 210211197805100156 1 2004/03/24 hi,this is description 5100156 hi,this is description 5100156 6
5100157 210211197805100157 2 1976/11/26 hi,this is description 5100157 hi,this is description 5100157 6
create index i_people_self_desc on people(self_desc);
create index i_people_self_desc_long on people(self_desc_long);
create bitmap index i_people_sex on people(sex);
create index i_people_birthdate on people(birth_date);alter table sc add constraint p_sc primary key(pno,cid);
create index i_sc_grade on sc(grade)
查询语句:统计60分以上,生日大于09年12月份,成绩大于等于95分的人的情况,数据量2W条。select
p.p_no,p.birth_date,sc.grade
from people p,sc
where p.p_no=sc.pno and sc.grade>=95
and p.birth_date>'1-dec-2009'
我试了下,如果people2建立分区(range分区)并且建立unique index(on p_no),单纯一个count行数,都要40多秒,不知这分区表到底是怎么用,是否能提高查询速度,普通表建好了主键,count不到1秒。
关联表用join。
p.birth_date>'1-dec-2009' 这个地方 birth_date是date型 右边to_date下
不然走不上索引
个人的浅见,分区表是在CPU有余力,数据输出瓶颈卡在硬盘的I/O时采取的方案你可以执行一下Explain ,看看CPU Cost 和 IO的数据