oracle表分区（一）

建立分区的好处：改善表的查询性能，使表更容易管理，便于备份和恢复，提高数据安全性。

注意：分区表不能有long或者raw long 数据类型。

 

一、分类

1、range（范围）分区

2、hash（哈希）分区

3、list（列表）分区

4、组合分区range-hash range-list。

 

二、分区选择条件

1、range分区

应用范围比较广泛的表分区方式，以列的值的范围来作为分区划分条件的，将记录放到列所在的range分区中，因此在创建的时候需要指定基于的列，以及分区的范围值，如果有些记录暂时无法预测范围，可以创建maxvalue分区，所有不在指定范围的记录都会到这个maxvalue分区中，并且支持多列作为依赖列。每个分区中的数据量不均匀

2、hash分区

应用于那些无法有效划分范围的表，这样对提高性能有所帮助，hash是将数据平均分配到你指定的几个分区中，列所在的分区时依据分区列hash值自动分配的，不能控制那条值在那个分区，支持多个依赖列。

3、list分区

需要指定列的值，单个分区值可以有多个值，其分区中的值必须明确指定，不能想range中指定范围，可以确定那个值在那个分区，但是只能有一个依赖列，分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入就会失败，因此在使用list分区时，建议创建一个default分区，用于存储那些不在指定范围内的记录，类似range的maxvalue分区。

4、组合分区

如果某表桉某列进行分区，仍然较大，或者是一些其他的需求，还可以通过分区内在建立子分区即组合分区。oracle10g中只有range-hash、range-list，根分区只能使用range分区，则分区可以是hash或者list分区。11g在组合分区功能上有所增强，range-range,list-range,list-list,list-hash，hash不能做根分区是因为数据分配是平均分配，某条记录在那个分区是不可知的。

 

三、分区创建

首先要介绍几个个数据字典表：user_part_tables、user_tab_partitions、user_tab_subpartitions

user_part_tables：记录分区表的信息

user_tab_partitions:记录表的分区信息

user_tab_subpartitions:查询表的子分区信息

 

1、range分区创建

创建一个标准的range分区表：

create table t_partition_range(id number,name varchar2(50))

paritition by range(id)(

partition t_range_p1 values less than (10) tablespace  tbspace01,

partition t_range_p2 values less than (20) tablespace  tbspace02,

partition t_range_p3 values less than (30) tablespace  tbspace03,

partition t_range_pmax values less than (maxvalue) tablespace  tbspace04

);

注：id：为分区依赖列（可以有多个，用逗号分隔）

      t_range_p1 ：分区的名称

      values less than:后跟分区范围值(如果依赖列有多个，范围对应值也应是多个，中间以逗号分隔);

      tbspace03:分区的存储属性，例如所在表空间等属性(可为空)，默认继承基表所在表空间的属性。

可以查看分别查看两张数据字典表有关表t_partition_range的信息。

 

2、hash分区创建

第一种方式：

create table t_partition_hash(id number,name varchar2(50))

partition by hash(id)(

partition t_hash_p1 tablespace tbspace01,

partition t_hash_p1 tablespace tbspace02,

partition t_hash_p1 tablespace tbspace03

);

第二种方式：

create table t_partition_hash2(id number,name varchar2(50))

partition by hash(id)

partitions 3 store in (tbspace01,tbspace02,tbspace03);

注：第二种方式说明可以直接指定分区数量和可供使用的表空间，表空间的数量不一定要等于分区的数量

 

3、list分区创建

create table t_partition_list (id number,name varchar2(50))
 partition by list(id)(
 partition t_list_p1 values (1,2,3,4,5,6,7,8,9) tablespace tbspart01,
 partition t_list_p2 values (10,11,12,13,14,15,16,17,18,19) tablespace tbspart02,
 partition t_list_p3 values (20,21,22,23,24,25,26,27,28,29) tablespace tbspart03,
 partition t_list_pd values (default) tablespace tbspart04);

注：list分区的依赖列只能有一个，values中的值即为id中的值。

 

4、range-hash 组合分区创建

为所有分区创建4个hash子分区

create table t_partition_rh (id number,name varchar2(50))
 partition by range(id) subpartition by hash(name)
 subpartitions 4 store in (tbspart01, tbspart02, tbspart03,tbspart04)(
 partition t_r_p1 values less than (10) tablespace tbspart01,
 partition t_r_p2 values less than (20) tablespace tbspart02,
 partition t_r_p3 values less than (30) tablespace tbspart03,
 partition t_r_pd values less than (maxvalue) tablespace tbspart04);

 

对某一个分区创建4个哈市子分区

create table t_partition_rh (id number,name varchar2(50))
 partition by range(id) subpartition by hash(name)(
 partition t_r_p1 values less than (10) tablespace tbspart01,
 partition t_r_p2 values less than (20) tablespace tbspart02,
 partition t_r_p3 values less than (30) tablespace tbspart03
 (subpartition t_r_p3_h1 tablespace tbspart01,
 subpartition t_r_p3_h2 tablespace tbspart02,
 subpartition t_r_p3_h3 tablespace tbspart03),
 partition t_r_pd values less than (maxvalue) tablespace tbspart04);

 

给各个分区创建不同的子分区：

create table t_partition_rh (id number,name varchar2(50))
 partition by range(id) subpartition by hash(name)(
 partition t_r_p1 values less than (10) tablespace tbspart01,
 partition t_r_p2 values less than (20) tablespace tbspart02
 (subpartition t_r_p2_h1 tablespace tbspart01,
 subpartition t_r_p2_h2 tablespace tbspart02),
 partition t_r_p3 values less than (30) tablespace tbspart03
 subpartitions 3 store in (tbspart01,tbspart02,tbspart03),
 partition t_r_pd values less than (maxvalue) tablespace tbspart04
 (subpartition t_r_p3_h1 tablespace tbspart01,
 subpartition t_r_p3_h2 tablespace tbspart02,
 subpartition t_r_p3_h3 tablespace tbspart03)
 );

 

分区模板应用：

在指定子分区依赖列以后，指定子分区的存储模板，各个分区即会按照子分区模式创建子分区

create table t_partition_rh (id number,name varchar2(50))
 partition by range(id) subpartition by hash(name)
 subpartition template (
 subpartition h1 tablespace tbspart01,
 subpartition h2 tablespace tbspart02,
 subpartition h3 tablespace tbspart03,
 subpartition h4 tablespace tbspart04)(
 partition t_r_p1 values less than (10) tablespace tbspart01,
 partition t_r_p2 values less than (20) tablespace tbspart02,
 partition t_r_p3 values less than (30) tablespace tbspart03,
 partition t_r_pd values less than (maxvalue) tablespace tbspart04);

 

 

5、range-list组合分区创建

其创建方式和range-hash极其相似，只是子分区是list分区，同样也可以使用分区模板

create table t_partition_rl (id number,name varchar2(50))
 partition by range(id) subpartition by list(name)
 subpartition template (
 subpartition l1 values ('aa') tablespace tbspart01,
 subpartition l2 values ('bb') tablespace tbspart02,
 subpartition l3 values ('cc') tablespace tbspart03,
 subpartition l4 values ('dd') tablespace tbspart04)(
 partition t_r_p1 values less than (10) tablespace tbspart01,
 partition t_r_p2 values less than (20) tablespace tbspart02,
 partition t_r_p3 values less than (30) tablespace tbspart03,
 partition t_r_pd values less than (maxvalue) tablespace tbspart04);

 

 

四、分区公共准则

1、如果选择的分区不能确保各个分区内记录量的基本平均，则这种分区方式有可能是不恰当的

比如对于range 分区，假设分了10 个分区，而其中一个分区中的记录数占总记录数的90%，其它9 个分区只占总记录数的10%，则这个分区方式就起不到数据平衡的作用。当然，如果你的目的并不是为了平衡，只是为了区分数据，ok，对于这种情况，我想说的是，你务必要意识到存在这个问题。

2、对于分区的表或索引，其所涉及的所有分区，其块的大小必须一致。