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;