Oracle表空间分区技术详解与实践总结

一、表空间及分区概念

表空间&＃xff1a;表空间是oracle中的一个逻辑概念&＃xff0c;一个数据库可以有一个或者多个表空间&＃xff0c;而一个表空间对应一个或者多个数据文件。表空间中容纳这许多的数据库实体&＃xff0c;如表、视图、索引等。

分区表&＃xff1a;随着表中数据量的不断变大&＃xff0c;数据表的访问效率会逐渐变慢&＃xff0c;这时就可以用到表分区了。表分区后&＃xff0c;逻辑上仍然是一张表&＃xff0c;但是在存储时&＃xff0c;会将一张表中的数据分割到不同的表空间中&＃xff0c;这样就减少了进行全表扫描的机会&＃xff0c;增加查询效率。

二、表分区分类

1、范围分区

范围分区将数据基于某个字段范围进行分区划分&＃xff0c;这里的分区字段及划分范围&＃xff0c;是在建表时指定的&＃xff0c;实际工作中使用日期作为范围分区的情况比较多。如下示例&＃xff0c;创建了一个员工表&＃xff0c;该表按照员工生日进行了分区&＃xff0c;这里生于1981年之前的放到分区emp_part1 &＃xff0c;生于1990至1980年的放到分区emp_part2&＃xff0c;生于1991年和1991年之后的&＃xff0c;放到分区emp_part3。

create table employer (id varchar(20),name varchar(50),birthday date,sex char(2))

partition by range (birthday)

(partition emp_part1 values less than (to_date(&＃39;1981-01-01&＃39;),&＃39;yyyy-MM-dd&＃39;)) tablespace tb_space1,

partition emp_part2 values less than (to_date(&＃39;1991-01-01&＃39;),&＃39;yyyy-MM-dd&＃39;)) tablespace tb_space2,

partition emp_part3 values less than (MAX_VALUE) tablespace tb_space3)

这里的MAX_VALUE是分区字段中的最大值。

2、列表分区

列表分区适用于某个字段的值只有几个值的情况&＃xff0c;如性别字段&＃xff0c;就可以作为列表分区的分区字段。如下示例&＃xff0c;同样是建立一个员工表&＃xff0c;但是这里是按照性别进行分区&＃xff1a;

create table employer (id varchar(20),name varchar(50),birthday date,sex char(2))

partition by list (sex)

(partition sex_part1 values(&＃39;01&＃39;) tablespace tb_space1,

partition sex_part2 values(&＃39;02&＃39;) tablespace tb_space2

)

3、散列分区

当列值散列排列时(如uuid)&＃xff0c;可以使用散列分区方式。这种分区方式会计算分区字段的hash值&＃xff0c;来决定该记录会被放在哪个分区。散列分区建议分区的数量为2的n次方个&＃xff0c;这样可以使数据排列更加均匀。如下所示&＃xff0c;将id作为分区字段对表进行了分区处理&＃xff1a;

create table employer (id varchar(20),name varchar(50),birthday date,sex char(2))

partition by hash(id)

(partition id_part1 tablespace tb_space1

partition id_part2 tablespace tb_space2

)

4、范围-列表分区

这种分区是基于范围和列表分区&＃xff0c;首先按照某列进行范围分区&＃xff0c;然后再按照某列进行列表分区&＃xff0c;分区之中的分区称为子分区。如下示例所示&＃xff1a;

create table employer (id varchar(20),name varchar(50),birthday date,sex char(2))

partition by range (birthday) subpartition by list(sex)

(partition p1 values less than (to_date(&＃39;1981-01-01&＃39;),&＃39;yyyy-MM-dd&＃39;)) tablespace tb_space1

(subpartition p1sub1 values(&＃39;01&＃39;) tablespace tb_space1,

subpartition p1sub2 values(&＃39;02&＃39;) tablespace tb_space1),

partition p2 values less than (to_date(&＃39;1991-01-01&＃39;),&＃39;yyyy-MM-dd&＃39;)) tablespace tb_space2

(subpartition p2sub1 values(&＃39;01&＃39;) tablespace tb_space2,

subpartition p2sub2 values(&＃39;02&＃39;) tablespace tb_space2)

)

这里员工表首先按照birthday字段进行范围分区&＃xff0c;然后在此基础上再按照sex字段进行范围分区&＃xff0c;其中p1sub1、p1sub2、p2sub1、p2sub2就是子分区。

5、范围-散列分区

与范围-列表分区类似&＃xff0c;范围-散列分区是基于范围分区和列表分区实现的。它首先根据某个字段对表进行范围分区&＃xff0c;然后再根据某个字段进行散列分区&＃xff0c;如下所示&＃xff1a;

create table employer (id varchar(20),name varchar(50),birthday date,sex char(2))

partition by range (birthday) subpartition by hash(id) subpartitions 3 store in (test_space1,test_space2,test_space3)

(partition p1 values less than (to_date(&＃39;1981-01-01&＃39;),&＃39;yyyy-MM-dd&＃39;)) tablespace tb_space1,

partition p2 values less than (to_date(&＃39;1991-01-01&＃39;),&＃39;yyyy-MM-dd&＃39;)) tablespace tb_space2)

三、分区相关操作

1、添加分区

alter table employer add partition p4 values less than  (to_date(&＃39;2001-01-01&＃39;),&＃39;yyyy-MM-dd&＃39;))

这里给employer表新增了一个分区p4。需要注意&＃xff0c;这里新增的分区要大于原来分区最后一个分区的界限。

2、删除分区

alter table employer drop partition p4。

删除employer表对应的p4分区。需要注意的是&＃xff0c;删除分区后&＃xff0c;分区对应的数据也会被删除&＃xff0c;同时表中的索引也会失效&＃xff0c;需要重新建立。

3、截断分区

截断分区是指删除某个分区中的数据&＃xff0c;但是不会删除分区&＃xff0c;也不会删除其它分区中的数据。如下&＃xff1a;

alter table employer truncate partition p2。

这里对employer表的p2字段进行了截断操作。

4、合并分区

合并分区是指将两个分区合并成一个分区&＃xff0c;合并后的分区将采用界限较高的分区界限&＃xff0c;如下所示&＃xff1a;

alter table employer merge partitions p1,p2 into partition p2。

这里将p1和p2分区数据都合并到了p2分区。

5、拆分分区

拆分分区就是将一个分区拆分为两个&＃xff0c;拆分后原来的分区将不再存在。需要注意的是&＃xff0c;散列分区的分区不能拆分。

alter table employer split partition p2 (to_date(&＃39;1991-01-01&＃39;),&＃39;yyyy-MM-dd&＃39;)) into (partition  p21,partition p22)。

这里将分区p2拆分为p21和p22&＃xff0c;拆分的界限是1991-01-01.

6、收缩分区 (coalesca)

通过收缩分区&＃xff0c;可以减少当前hash分区的数量。但是这种操作只能再散列分区上进行。

alter table employer coalesca partition。