Oracle 表分区(Partition)

  表分区功能能够改善应用程序性能,提高数据库可管理性和可用性,是数据库管理非常关键的技术。数据库通过使用分区提高查询性能,简化日常管理维护工作。

  1 分区优点

  1) 减少维护工作量,独立管理每个表分区比管理整个大表要轻松的多

  2) 增加数据库的可用性,由于将数据分散到各个分区中,减少了数据损坏的可能性

  3) 均衡I/O,减少竞争,通过把表的不同分区分配到不同的磁盘来平衡I/O改善性能

  4) 分区对用户保持透明,用户感受不到它的存在

  5) 提高查询速度,对于大表的DML操作可以分解到表的不同分区来执行,可以加快执行速度

  

  2 分区缺点

  已经存在的表,不能直接转化为分区表

  3 什么时候使用分区表

1) 表的大小超过2GB
2) 表中包含历史数据,新的数据被增加到新的分区中
 
4 分区类型
1) Range 分区
2) HASH分区(散列分区)
3) 列表分区
4) 组合分区(复合分区)
 
 
 1) Range Partition

Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中。

如按照时间划分,2017年第一季度的数据放到第一分区,二季度的数据放到第二分区,在创建的时候,需要指定基于的列,以及分区的范围值。在按时间分区时, 如果某些记录暂无法预测范围,可以创建 maxvalue 分区,所有不在指定范围内的记录都会被存储到 maxvalue 所在分区中。

假设有一个emp表,表中有数据200000行,我们将此表通过hire_date进行分区,每个分区存储50000行,我们将每个分区保存到单独的表空间中,这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码,如下:

create table emp_range
(
empno number not null primary key,
deptno number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
status char(1),
hire_date date not null
)
partition by range(hire_date)
(
partition hire_part1 values less than(to_date('2017-04-01','yyyy-mm-dd')) tablespace emp_space01,
partition hire_part2 values less than(to_date('2017-07-01','yyyy-mm-dd')) tablespace emp_space02,
partition hire_part3 values less than(to_date('2017-10-01','yyyy-mm-dd')) tablespace emp_space03,
partition hire_part4 values less than(to_date('2018-01-01','yyyy-mm-dd')) tablespace emp_space04
);

测试数据

insert into emp_range(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 10,'latiny1','liu','1', to_date('2017-01-02','yyyy-mm-dd'));

insert into emp_range(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 20,'latiny2','liu2','1', to_date('2017-04-02','yyyy-mm-dd'));

insert into emp_range(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 30,'latiny3','liu3','1', to_date('2017-07-02','yyyy-mm-dd'));

insert into emp_range(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 40,'latiny4','liu4','1', to_date('2017-10-02','yyyy-mm-dd'));

按分区查询结果

select *
from emp_range partition(hire_part1);

2) HASH分区

散列分区是在列值上使用散列算法,以确定将行放入哪个分区中。当列的值没有合适的条件时,建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。

create table emp_hash
(
empno number not null primary key,
deptno number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
status char(1),
hire_date date not null
)
partition by hash(deptno)
(
partition dep_part1 tablespace emp_space01,
partition dep_part2 tablespace emp_space02,
partition dep_part3 tablespace emp_space03
);

测试数据

insert into emp_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 10,'latiny1','liu','1', to_date('2017-01-02','yyyy-mm-dd'));

insert into emp_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 20,'latiny2','liu2','1', to_date('2017-04-02','yyyy-mm-dd'));

insert into emp_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 30,'latiny3','liu3','1', to_date('2017-07-02','yyyy-mm-dd'));

insert into emp_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 40,'latiny4','liu4','1', to_date('2017-10-02','yyyy-mm-dd'));

insert into emp_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 50,'latiny5','liu5','1', to_date('2017-11-02','yyyy-mm-dd'));

insert into emp_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 60,'latiny6','liu6','1', to_date('2017-08-02','yyyy-mm-dd'));

select *
from emp_hash partition(dep_part1);

散列分区最主要的机制是根据Hash算法来计算具体某条纪录应该插入到哪个分区中, Hash算法中最重要的是Hash函数,Oracle中如果你要使用Hash分区,只需指定分区的数量即可。建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。

3) 列表分区
该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。

create table emp_list
(
empno number not null primary key,
deptno number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
status char(1),
hire_date date not null

)
partition by list(status)
(
partition status_part1 values('1') tablespace emp_space01,
partition status_part2 values('0') tablespace emp_space02
);

insert into emp_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 10,'latiny1','liu','1', to_date('2017-01-02','yyyy-mm-dd'));

insert into emp_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 20,'latiny2','liu2','0', to_date('2017-04-02','yyyy-mm-dd'));

insert into emp_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 30,'latiny3','liu3','1', to_date('2017-07-02','yyyy-mm-dd'));

insert into emp_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 40,'latiny4','liu4','0', to_date('2017-10-02','yyyy-mm-dd'));

insert into emp_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 50,'latiny5','liu5','1', to_date('2017-11-02','yyyy-mm-dd'));

insert into emp_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 60,'latiny6','liu6','0', to_date('2017-08-02','yyyy-mm-dd'));

select *
from emp_list partition(status_part2);

 
4) 组合分区
 
范围--散列分区
这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。

create table emp_range_hash
(
empno number not null primary key,
deptno number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
status char(1),
hire_date date not null
)
partition by range(hire_date)subpartition by hash(deptno) subpartitions 4 store in (emp_space01,emp_space02,emp_space03,emp_space04)
(
partition part_01 values less than(to_date('2017-04-01','yyyy-mm-dd')),
partition part_02 values less than(to_date('2017-07-01','yyyy-mm-dd')),
partition part_03 values less than(to_date('2017-10-01','yyyy-mm-dd')),
partition part_04 values less than(to_date('2018-01-01','yyyy-mm-dd'))
);

-- 测试数据
insert into emp_range_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 10,'latiny1','liu','1', to_date('2017-01-02','yyyy-mm-dd'));

insert into emp_range_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 20,'latiny2','liu2','0', to_date('2017-04-02','yyyy-mm-dd'));

insert into emp_range_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 30,'latiny3','liu3','1', to_date('2017-07-02','yyyy-mm-dd'));

insert into emp_range_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 40,'latiny4','liu4','0', to_date('2017-10-02','yyyy-mm-dd'));

insert into emp_range_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 50,'latiny5','liu5','1', to_date('2017-11-02','yyyy-mm-dd'));

insert into emp_range_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 60,'latiny6','liu6','0', to_date('2017-08-02','yyyy-mm-dd'));

insert into emp_range_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 20,'latiny7','liu7','1', to_date('2017-01-02','yyyy-mm-dd'));

insert into emp_range_hash(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 30,'latiny8','liu8','1', to_date('2017-01-02','yyyy-mm-dd'));

select *
from emp_range_hash partition(part_01);

范围--列表分区
这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。

create table emp_range_list
(
empno number not null primary key,
deptno number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
status char(1),
hire_date date not null
)
partition by range(hire_date) subpartition by list(status)
(
partition part_01 values less than(to_date('2017-04-01','yyyy-mm-dd')) tablespace emp_space01
(
subpartition p1sub1 values('1') tablespace emp_space01,
subpartition p1sub2 values('0') tablespace emp_space01
),

partition part_02 values less than(to_date('2017-07-01','yyyy-mm-dd')) tablespace emp_space02
(
subpartition p2sub1 values('1') tablespace emp_space02,
subpartition p2sub2 values('0') tablespace emp_space02
)
);

-- 测试数据
insert into emp_range_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 10,'latiny1','liu','1', to_date('2017-01-02','yyyy-mm-dd'));

insert into emp_range_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 20,'latiny2','liu2','0', to_date('2017-04-02','yyyy-mm-dd'));

insert into emp_range_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 30,'latiny3','liu3','1', to_date('2017-06-02','yyyy-mm-dd'));

insert into emp_range_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 40,'latiny4','liu4','1', to_date('2017-05-02','yyyy-mm-dd'));

insert into emp_range_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 30,'latiny5','liu5','0', to_date('2017-06-02','yyyy-mm-dd'));

insert into emp_range_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 40,'latiny6','liu6','0', to_date('2017-05-02','yyyy-mm-dd'));

insert into emp_range_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 10,'latiny7','liu7','0', to_date('2017-01-02','yyyy-mm-dd'));

insert into emp_range_list(empno, deptno, first_name, last_name, status, hire_date)
values (seq_par_id.nextval, 20,'latiny8','liu8','1', to_date('2017-01-02','yyyy-mm-dd'));

select *
from emp_range_list partition(part_01);

5 分区管理维护

1)添加表分区

对于已经存在表分区的表,如果要添加一个新的表分区,使用如下语法(我们对范围分区表实例添加一个新的分区):

alter table emp_range
add partition hire_date5
values less than (to_date('2018-04-01','yyyy-mm-dd')) tablespace emp_space0;

-- 注意:以上添加的分区界限应该高于最后一个分区界限。

给emp_range_list 表的part_02 分区添加一个子分区,p2sub3:

alter table emp_range_list modify partition part_02 add subpartition p2sub3 values('3') tablespace emp_space02;

 
上一篇:打印出从1到最大的n位十进制数


下一篇:java有关构造器的面试题详解