对于分区来说,概念理解还不如看一个实实在在的例子来的爽一些,不用多说看例子:
1、范围分区 /*根据表中的某个字段的之来进行分区,数据插入的时候会插入到相应的分区中*/
create table graderecord(/*创建一个学生成绩的表*/
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(30),
grade int
)
partition by range(grade)----创建范围分区,根据grade字段
(
partition bujige values less than(60),---不及格
partition jigevalues values less than(85),--及格
partition youxiu values less than(maxvalue)--优秀
);
插入数据
- insert into graderecord values('511601','魁','229',92);
- insert into graderecord values('511602','凯','229',62);
- insert into graderecord values('511603','东','229',26);
- insert into graderecord values('511604','亮','228',77);
- insert into graderecord values('511605','敬','228',47);
- insert into graderecord(sno,sname,dormitory) values('511606','峰','228');
- insert into graderecord values('511607','明','240',90);
- insert into graderecord values('511608','楠','240',100);
- insert into graderecord values('511609','涛','240',67);
- insert into graderecord values('511610','博','240',75);
- insert into graderecord values('511611','铮','240',60);
查询全部数据
select * from graderecord;
select * from graderecord partition(bujige);
select * from graderecord partition(jige);
select * from graderecord partition(youxiu);
以上就是简单的范围分区的语法和使用。
2、散列分区,也就是HASH分区
create table graderecord(/*创建一个学生成绩的表*/
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(30),
grade int
)
partition by hash(sno)----创建范围分区,根据grade字段
(
partition p1,
partition p2,
partition p3,
);
查询的时候使用partition
select * from graderecord partition(p1);
select * from graderecord partition(p2);
select * from graderecord partition(p3);
查询出来的结果每次有可能不一样,HASH算法由Oracle决定
3、列表分区-----list
create table graderecord(/*创建一个学生成绩的表*/
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(30),
grade int
)
partition by list(dormitory)(-----按宿舍分区
partition p229 values ('229'),
partition p228 values('288'),
partition p204 values ('240')
)
select * from graderecord partition('p229');
p229分区所得数据如下:
select * from graderecord partition('p228');
p228分区所得数据如下:
select * from graderecord partition('p240);
p240分区所得数据如下:
4、复合分区
首先讲范围-散列分区。先声明一下:列表分区不支持多列,但是范围分区和哈希分区支持多列。
代码如下:
- create table graderecord
- (
- sno varchar2(10),
- sname varchar2(20),
- dormitory varchar2(3),
- grade int
- )
- partition by range(grade)
- subpartition by hash(sno,sname)
- (
- partition p1 values less than(75)
- (
- subpartition sp1,subpartition sp2
- ),
- partition p2 values less than(maxvalue)
- (
- subpartition sp3,subpartition sp4
- )
- );
create table graderecord
(
sno varchar2(10),
sname varchar2(20),
dormitory varchar2(3),
grade int
)
partition by range(grade)
subpartition by hash(sno,sname)
(
partition p1 values less than(75)
(
subpartition sp1,subpartition sp2
),
partition p2 values less than(maxvalue)
(
subpartition sp3,subpartition sp4
)
);
以grade划分范围,然后以sno和sname划分散列分区,当数据量大的时候散列分区则趋于“平均”。
插入数据:
- insert into graderecord values('511601','魁','229',92);
- insert into graderecord values('511602','凯','229',62);
- insert into graderecord values('511603','东','229',26);
- insert into graderecord values('511604','亮','228',77);
- insert into graderecord values('511605','敬','228',47);
- insert into graderecord(sno,sname,dormitory) values('511606','峰','228');
- insert into graderecord values('511607','明','240',90);
- insert into graderecord values('511608','楠','240',100);
- insert into graderecord values('511609','涛','240',67);
- insert into graderecord values('511610','博','240',75);
- insert into graderecord values('511611','铮','240',60);
- insert into graderecord values('511612','狸','244',72);
- insert into graderecord values('511613','杰','244',88);
- insert into graderecord values('511614','萎','244',19);
- insert into graderecord values('511615','猥','244',65);
- insert into graderecord values('511616','丹','244',59);
- insert into graderecord values('511617','靳','244',95);
insert into graderecord values('511601','魁','229',92);
insert into graderecord values('511602','凯','229',62);
insert into graderecord values('511603','东','229',26);
insert into graderecord values('511604','亮','228',77);
insert into graderecord values('511605','敬','228',47);
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');
insert into graderecord values('511607','明','240',90);
insert into graderecord values('511608','楠','240',100);
insert into gr