Oracle 11gR2 表分区机制汇总及优缺点比较

Oracle表分区机制汇总：

一、概述：
    分区有利于管理非常大的表和索引，它使用了一种“分而治之”的逻辑。分区引入了一种分区键（partition key）的概念，分区键用于根据某个区间值（或范围值）、特定值列表或散列函数值执行数据的聚集。这样做好处如下： 
    （1）提高数据的可用性： 
    （2）减轻管理的负担。
    （3）改善某些查询的性能：
    （4）可以把修改分布到多个单独的分区上，从而减少大容量OLTP系统上的竞争

二、表分区几种类型
    （1）区间分区:可以指定应当存储在一起的数据区间
                  优点：可以用作组合分区，最普通用得最频繁的分区方式
                  缺点：DBA必须定期创建分区
        例子：
 ops$tkyte@ORA10GR1> 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'))
     )
  /
 Table created.
        上面的列子中，如果data大于2006年1月1日，数据插入就会报错。
 ops$tkyte@ORA10GR1> insert into range_example
            ( range_key_column, data )
     values 
            ( to_date( '15/12/2007 00:00:00',
            'dd/mm/yyyy hh24:mi:ss' ),
            'application data...' );

 insert into range_example
  * 
 ERROR at line 1: 
 ORA-14400: inserted partition key does not map to any partition

        DBA必须创建保存2006年数据的分区。也可以指定一个上限分区这样就不会出现上述的错误，但是所有大于2006年的数据都在一个分区中，与我们的分区初衷违背：
 ops$tkyte@ORA10GR1> 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)
     )
  /
       

    （2）散列分区:利用哈希算法确定分区
                   优点：实在没有可用的分区列时才使用。
                   缺点：不能根据业务需要规定特定的行保存在特定的分区。如果你由于某种原因希望将某个特定行放在分区PART_1中，就不应该使用散列分区。正常情况下使用的较少。
        例子：
 ops$tkyte@ORA10G> 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
     )
     /
 Table created.
        由于不常用，这里不详细介绍了。

    （3）列表分区：根据某个代码来进行分区（如州代码或区代码）。
     优点：适合在分区的建值能够全部枚举的情况下（比如按照每个地区建一个分区）。
                   缺点：如果你的业务系统常用的查询是按时间段而不是按照你的分区键（比如地区），那么你就白瞎了，分区没有任何提高性能。
 ops$tkyte@ORA10G> 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' )
     )
     /
 Table created.
 就像区间分区一样，如果我们想插入列表分区中未指定的一个值,就会报错。
 ops$tkyte@ORA10G> insert into list_example values ( 'VA', 'data' );
 insert into list_example values ( 'VA', 'data' )
   * 
 ERROR at line 1: 
 ORA-14400: inserted partition key does not map to any partition
 与区间分区类似，可以指定默认值来保存找不到分区键值的记录。
 ops$tkyte@ORA10G> alter table list_example
     add partition 
     part_3 values ( DEFAULT );
 Table altered.    

 ops$tkyte@ORA10G> insert into list_example values ( 'VA', 'data' );
 1 row created.

    （4）间隔分区:Orale 11G的新特性，它与区间分区很类似。间隔分区是以一个区间分区表为“起点”，在定义中增加一个规则（即间隔），使得数据库知道将来如何自动增加分区。
             优点：不需要预先为数据创建分区，Oracle能自动的按照规则（如可以按年或月等）在有新数据插入时自动建立分区。这个特性太好了，妈妈再也不用担心我忘记加分区而使得插入数据失败了。
             缺点：1.分区键必须是能够增加number或interval类型的值（比如数值、日期等）。按varchar2字段分区就不能进行间隔分区，因为无法让varvhar2与某个数字间隔相加。
                   2.不能再组合分区中使用。
             例子
 create table audit_trail
 (ts    timestamp,
  data  varchar2(30)
 )
 partition by range(ts)
 interval (numtoyminterval(1,'month'))
 store in (users,example)
 (partition p0 values less than (to_date('01-01-1900','dd-mm-yyyy'))
 )
 /
 Table created.

 1）对于采用date类型的Interval Partitioning可以支持按year、month、day、hour、minute、second六种类型的定长宽度分区，分别通过如下函数转换  numtoyminterval ( n, { 'YEAR'|'MONTH'})  numtodsinterval ( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'}) 。
 2）对于采用number类型的Interval Partitioning必须按照固定的宽度分区
 3）store in (users,example)用来指定分区保存在哪个表空间，如果这里写了多个表空间，Oracle会自动根据分区间隔分配。
        4）1900年1月1日之前的数据会插入分区p0。大于1900年1月1日之后的数据每月只要有数据，就会自动创建一个分区。

    （5）引用分区:Orale 11G的新特性,它处理的是父/子对等分区的问题。采用引用分区时，子表会继承其父表的分区机制。
             优点：如果有父子关系的表需要分区时（比如订单和订单明细表）非常方便。
             缺点：必须指定外键。
 create table orders
 (
  order#     number primary key,
  order_date date,
  data       varchar2(30)
 )
 enable row movement
 partition by range (order_date)
 (
   partition part_2009 values less than (to_date('01-01-2010','dd-mm-yyyy')),
   partition part_2010 values less than (to_date('01-01-2011','dd-mm-yyyy'))
 )
 /
 Table created.

 create table order_line_items
 (
  order#         number,
  line#          number,
  data           varchar2(30),
  constraint c1_pk primary key(order#,line#),
  constraint c1_fk_p foreign key(order#) references orders    --这里必须指定外键
 )
 enable row movement
 partition by reference(c1_fk_p)
 /
 Table created.
       
        神奇之处就是分区规则指定的 "partition by reference(c1_fk_p)"这一行。

    （6）组合分区：组合分区是区间分区、散列分区以及列表分区的组合。（这个不常用，这里就不详细介绍了，给个例子）
      重点是Oracle版本支持的组合方式列表：
                  区间              列表            散列
    区间        11gR1           9iR2            9iR1
    列表        11gR1           11gR1         11gR1
    散列        11gR2           11gR2         11gR2
        例子：
 ops$tkyte@ORA10G> CREATE TABLE composite_example
 2 ( range_key_column date,
 3 hash_key_column int,
 4 data varchar2(20)
 5 )
 6 PARTITION BY RANGE (range_key_column)
 7 subpartition by hash(hash_key_column) subpartitions 2
 8 (
 9 PARTITION part_1
 10 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
 11 (subpartition part_1_sub_1,
 12 subpartition part_1_sub_2
 13 ),
 14 PARTITION part_2
 15 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
 16 (subpartition part_2_sub_1,
 17 subpartition part_2_sub_2
 18 )
 19 )
 20 /
 Table created.

三、总结
    最常用最简单的应该是“区间分区”，但是11G以后添加的“间隔分区” 和“引用分区”的特性非常的好，建议11G及以上的ORACLE版能用的尽量用“间隔分区” 和“引用分区”。