Oracle分区表

最近因为业务上客户数据的表空间占用过大，而想通过删除一部分数据来缩小表空间文件的大小无法实现，故想到使用分区表来实现

分区表分为范围分区(range)、哈希分区(hash)、列表分区(list)、以及一些组合分区(range-hash、range-list)

以下为各个分区的创建语法：

1、范围分区(range)

简介：

语法：

1 　　　　CREATE TABLE 表名 (列定义) partition byrange(列名)
2 (
3 　　　　　　partition  分区名  valuesless than (值)，
4 。。。。
5 　　　　　　partition 分区名 valuesless than (maxvalue)　　###默认分区
6 　　　　　);

2、哈希分区(hash)

简介：

语法：

1 　　　　CREATE TABLE 表名 (列定义) partition byhash(列名)
2 (
3 partition  分区名  tablespace 表空间名,
4 。。。。
5 partition 分区名  tablespace 表空间名
6 　　　　　);

View Code

注:指定每个分区的表空间，用于分散在各个数据文件中。

3、列表分区(list)

简介：

需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列作为分区依赖列，但它的单个分区对应值可以是多个。

注：在列表分区中，必须明确分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用列表分区时，要创建一个default分区存储那些不存在指定范围内的记录。

语法：

1 　　　　CREATE TABLE 表名 (列定义) partition bylist(列名)
2 (
3 　　　　　　partition  分区名 values(值1,值2,值3....),
4 。。。。
5 　　　　　　partition 分区名  values (default)
6 　　　　　);

View Code

4、组合分区

简介：

如果某表按照某列分区之后，仍然较大或其他需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

语法：

分区表的常见操作：

查询分区表：

selecttable_name,partition_name fromuser_tab_partitions where table_name ='表名';

查询对应分区表中数据

select*from 表名 partition (分区名);

添加分区：

altertable 表名 add partition values(值) tablespace 表空间名; 　

拆分分区(split)：　　

alter table 表名 split partition 分区名 {at | values} (值)
  into (partition 新分区名 tablespace 空间名,partition 新分区名 tablespace 空间名....);

注：标红处，列表为range类型，使用at；列表为list类型使用values；

合并分区(merge)：

相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来值较高的分区，原来的局部牵引相应也会合并，全局索引会失效，需要rebuild。

altertable 表名 merge partitions 分区名1，分区名2... into partition 新分区名;

移动分区(move)

altertable 表名 move partition 分区名 tablespace 空间名;

注：分区移动会自动维护局部分区索引，Oracle不会自动维护全局索引，所以需要重新rebuild分区索引。

提升：具体需要rebuild哪些索引，可以通过dba_part_indexs，dba_ind_partitions去判断

select index_name,status from user_indexs where table_name ='表名';

查询局部索引：

select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name ='索引名'；

select index_owner,index_name,partition_name from dba_ind_partitions where index_name ='索引名';

删除分区数据(truncate)

altertable 表名 truncate partition (分区名);

注：turncate相对delete操作快，数据仓库中的大量数据的批量数据加载可能会有用到；

截断分区同样会自动维护局部分区索引，同时会使全局索引unusable，需要重建。

删除分区(drop)

altertable 表名 drop partition 分区名;

同样会自动维护局部分区索引，同时会使全局索引unusable，需要重建。

分区索引

分为本地索引(local index) 和 全局索引(global index)，局部索引比全局索引容易管理而全局索引比较快。

本地索引

语法：

1 CREATE INDEX IND_表名 on表名(列)
2 local (
3 partition 分区名 tablespace 空间名,
4 partition 分区名 tablespace 空间名,
5         ....);

View Code