Oracle表分区机制介绍

Oracle表分区机制介绍

 

  一. 概念

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

 

      二. 表分区机制概述

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

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

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

 

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

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

      三. 各种分区机制介绍

      1. 区间分区

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

 

Sql代码  

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(&＃39;01/01/2005&＃39;,&＃39;dd/mm/yyyy&＃39;)),  

 PARTITION part_2 VALUES LESS THAN  

 (to_date(&＃39;01/01/2006&＃39;,&＃39;dd/mm/yyyy&＃39;)),  

 PARTITION part_3 VALUES LESS THAN  

 (MAXVALUE)  

 )  

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

   www.2cto.com  

      2. 散列分区

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

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

     创建散列分区表示例：

   www.2cto.com  

Sql代码  

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代码示例：

   www.2cto.com  

Sql代码  

create table list_example  

( state_cd varchar2(2),  

data varchar2(20)  

)  

partition by list(state_cd)  

( partition part_1 values ( &＃39;ME&＃39;, &＃39;NH&＃39;, &＃39;VT&＃39;, &＃39;MA&＃39; ),  

partition part_2 values ( &＃39;CT&＃39;, &＃39;RI&＃39;, &＃39;NY&＃39; )  

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

 

Sql代码  

alter table list_example  

 add partition  

 part_3 values ( DEFAULT );  

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

 

    4. 组合分区

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

   www.2cto.com  

     区间分区加散列分区

Sql代码  

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(&＃39;01/01/2005&＃39;,&＃39;dd/mm/yyyy&＃39;))  

 (subpartition part_1_sub_1,  

 subpartition part_1_sub_2  

 ),  

 PARTITION part_2  

 VALUES LESS THAN(to_date(&＃39;01/01/2006&＃39;,&＃39;dd/mm/yyyy&＃39;))  

 (subpartition part_2_sub_1,  

 subpartition part_2_sub_2  

 )  

 )  

     区间分区加列表分区

   www.2cto.com  

Sql代码  

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(&＃39;01/01/2005&＃39;,&＃39;dd/mm/yyyy&＃39;))  

 (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(&＃39;01/01/2006&＃39;,&＃39;dd/mm/yyyy&＃39;))  

 (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功能或对一个区间分区使用并行索引扫描，则建议在区间分区中再使用散列或列表分区。