Oracle 表分区机制介绍

 

      一. 概念

      一个表被分成了很多个独立的分区，那么每一个分区应该存储哪些数据，这就是表分区机制要解决的问题。

 

      二. 表分区机制概述

      目前oracle中有四种分区机制：

      1. 区间分区：可以指定应当存储在一起的数据区间。例如，时间戳在Jan-2005内的所有记录都存储在分区1中，时间戳在Feb-2005内的所有记录都存储在分区2中，依此类推。

      2. 散列分区：这是指在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。具有很大随机性，你不知道数据会被散列到哪个分区中去，但是一般每个分区存储的数据都差不多大小。

      3. 列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放在分区1中，STATUS值在（‘D’,’P’,’Q’）中的行放在分区2中，依此类推。

      4. 组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，你可以先对某些数据应用区间分区，再在区间中根据散列或列表来选择最后的分区。

 

      三. 各种分区机制介绍

      1. 区间分区

       建表语句示例：（用得最频繁）

 

CREATE TABLE range_example
 ( range_key_column date ,
 data varchar2(20)
 )
 PARTITION BY RANGE (range_key_column)
 ( PARTITION part_1 VALUES LESS THAN
 (to_date('01/01/2005','dd/mm/yyyy')),
 PARTITION part_2 VALUES LESS THAN
 (to_date('01/01/2006','dd/mm/yyyy')),
 PARTITION part_3 VALUES LESS THAN
 (MAXVALUE)
 )

      这样，日期小于2005年的数据放在part_1分区，日期是2005年的数据放在part_2分区，日期大于2005年的数据放在part_3分区。

 

      2. 散列分区

      对一个表执行散列分区时，oracle会对分区键运用一个散列函数，依此确定数据应该放在N个分区中哪一个。Oracle建议N是2的一个幂（2,4,8,16等），从而得到最佳的总体分布。

      散列分区是为了使数据尽可能均匀地分布在多个不同的分区上，选择分区键非常重要，如果选得不好，数据就会分布不均。建议分区键应该是唯一的列（比如主键）或者唯一的一组列（多列组成的主键）。 假如你选择的分区键只可能有4个相异的值，并使用2个分区，那么可能将所有的数据散列到同一分区上。

     创建散列分区表示例：

 

CREATE TABLE hash_example
 ( hash_key_column date,
 data varchar2(20)
 )
 PARTITION BY HASH (hash_key_column)
 ( partition part_1 tablespace p1,
 partition part_2 tablespace p2
 )

 

      3. 列表分区

      列表分区（list partitioning）是Oracle9i Release 1的一个新特性。它提供了这样一种功能，可以根据离散的值列表来指定一行位于哪个分区。如果能根据某个代码来进行分区（如州代码或区代码），这通常很有用。例如，你可能想把Maine州（ME）、New Hampshire州（NH）、Vermont州（VT）和Massachusetts州（MA）中所有人的记录都归至一个分区中，因为这些州相互之间挨得很近，而且你的应用按地理位置来查询数据。类似地，你可能希望将Connecticut州（CT）、Rhode Island州（RI）和New York州（NY）的数据分组在一起。

     SQL代码示例：

 

 create table list_example
 ( state_cd varchar2(2),
 data varchar2(20)
 )
 partition by list(state_cd)
 ( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),
 partition part_2 values ( 'CT', 'RI', 'NY' )
)

    这样创建之后，如果想插入不在这七个地方的数据就会报错。比如：insert into list_example values ( 'VA', 'data' );这个时候应该加一个默认分区，SQL如下：

 

alter table list_example
 add partition
 part_3 values ( DEFAULT );

    但是表一旦有了默认分区，不可以继续添加其他分区了，因为默认分区可能包含其他分区的数据。

 

    4. 组合分区

     其实就是上述分区的组合，区间分区加散列分区，区间分区加列表分区。就是区间分区之后，感觉每一个分区数据量还是很大，继续在分区上进行分区。代码示例如下：

 

     区间分区加散列分区

CREATE TABLE composite_example
 ( range_key_column date,
 hash_key_column int,
 data varchar2(20)
 )
  PARTITION BY RANGE (range_key_column)
 subpartition by hash(hash_key_column) subpartitions 2
 (
 PARTITION part_1
 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
 (subpartition part_1_sub_1,
 subpartition part_1_sub_2
 ),
 PARTITION part_2
 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
 (subpartition part_2_sub_1,
 subpartition part_2_sub_2
 )
 )

 

     区间分区加列表分区

 

CREATE TABLE composite_range_list_example
 ( range_key_column date,
 code_key_column int,
 data varchar2(20)
 )
 PARTITION BY RANGE (range_key_column)
 subpartition by list(code_key_column) 697 / 860
 (
 PARTITION part_1
 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
 (subpartition part_1_sub_1 values( 1, 3, 5, 7 ),
 subpartition part_1_sub_2 values( 2, 4, 6, 8 )
 ),
 PARTITION part_2
 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
 (subpartition part_2_sub_1 values ( 1, 3 ),
 subpartition part_2_sub_2 values ( 5, 7 ),
 subpartition part_2_sub_3 values ( 2, 4, 6, 8 )
 )
 )

 

    总之，，如果可以按某个属性自然地对数据完成区间分区，就应该使用区间分区，而不是散列分区或列表分区。散列和列表分区能提供分区的许多突出优点，但是在分区消除方面都不如区间分区有用。如果所得到的区间分区太大，不能很好地管理；或者如果你想使用所有PDML功能或对一个区间分区使用并行索引扫描，则建议在区间分区中再使用散列或列表分区。