ORACLE各种PARTITION的分析

ORACLE各种PARTITION的分析

 

ORACLE 为构建数据仓库提供了4种类型的分区方法：Range Partition ,Hash Partition ,List Partition,Composite Partition. 

下面我分别对这四种分区方法的概念，他们的使用场景，以及各种分区方法做一个性能比较。 

一：概念 

1：Range Partitioning 

这是最常用的一种分区方法，基于COLUMN的值范围做分区，最常见的是基于时间字段的数据的范围的分区，比如：对于SALE表，可以对销售时间按照月份做一个Range Partitioning。这种分区在数据仓库里用的比较多，以下是CREATE STATMENT 

CREATE TABLE sales_range 

(salesman_id  NUMBER(5), 

salesman_name VARCHAR2(30), 

sales_amount  NUMBER(10), 

sales_date    DATE) 

COMPRESS 

PARTITION BY RANGE(sales_date) 

(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE(&＃39;02/01/2000&＃39;,&＃39;DD/MM/YYYY&＃39;)), 

PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE(&＃39;03/01/2000&＃39;,&＃39;DD/MM/YYYY&＃39;)), 

PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE(&＃39;04/01/2000&＃39;,&＃39;DD/MM/YYYY&＃39;)), 

PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE(&＃39;05/01/2000&＃39;,&＃39;DD/MM/YYYY&＃39;))); 

对于COMPRESS关键字的理解，将在后续的压缩分区讲到 

 

2;Hash Partitioning 

Hash Partitioning映射数据到基于HASH算法的分区上，HASH算法将应用你指定的分区关键字，平均的分那些在Partitions中的行。给每一个分区近似相同的大小，要保证数据能平均分配，分区数一般是2N。比如说，需要insert sales_hash 一条数据，ORACLE会通过HASH算法处理salesman_id，然后找到对于的分区表进行insert。Hash Partitioning 是为跨越设备的分布式数据提供了一种理想的方法，HASH算法也很容易转化成RANGE分区方法，特别是当被分区的数据不是历史数据时。 

CREATE TABLE sales_hash 

(salesman_id  NUMBER(5), 

salesman_name VARCHAR2(30), 

sales_amount  NUMBER(10), 

week_no       NUMBER(2)) 

PARTITION BY HASH(salesman_id) 

PARTITIONS 4; 

 

3：List Partitioning 

List Partitioning能够让你明确的控制有多少行被分区，你能对要分区的COLUMN上明确的指定按照那些具体的值来分区，这种方式在Range和Hash方式是做不到的。这种方式的优点是，你能组织和分组那些没有顺序和没有关系的数据集。下面是通过销售地区做一个List分区表。 

CREATE TABLE sales_list 

(salesman_id  NUMBER(5), 

salesman_name VARCHAR2(30), 

sales_state   VARCHAR2(20), 

sales_amount  NUMBER(10), 

sales_date    DATE) 

PARTITION BY LIST(sales_state) 

(PARTITION sales_west VALUES(&＃39;California&＃39;, &＃39;Hawaii&＃39;) COMPRESS, 

PARTITION sales_east VALUES(&＃39;New York&＃39;, &＃39;Virginia&＃39;, &＃39;Florida&＃39;), 

PARTITION sales_central VALUES(&＃39;Texas&＃39;, &＃39;Illinois&＃39;)); 

 

4：Composite Partitioning 

Composite Partitioning 是把Range ,Hash ,List 分区方式组合起来的分区方式。 

比如Composite Range-Hash Partitioning和Composite Range-List Partitioning： 

CREATE TABLE sales_range_hash( 

  s_productid  NUMBER, 

  s_saledate   DATE, 

  s_custid     NUMBER, 

  s_totalprice NUMBER) 

PARTITION BY RANGE (s_saledate) 

SUBPARTITION BY HASH (s_productid) SUBPARTITIONS 8 

(PARTITION sal99q1 VALUES LESS THAN (TO_DATE(&＃39;01-APR-1999&＃39;, &＃39;DD-MON-YYYY&＃39;)), 

  PARTITION sal99q2 VALUES LESS THAN (TO_DATE(&＃39;01-JUL-1999&＃39;, &＃39;DD-MON-YYYY&＃39;)), 

  PARTITION sal99q3 VALUES LESS THAN (TO_DATE(&＃39;01-OCT-1999&＃39;, &＃39;DD-MON-YYYY&＃39;)), 

  PARTITION sal99q4 VALUES LESS THAN (TO_DATE(&＃39;01-JAN-2000&＃39;, &＃39;DD-MON-YYYY&＃39;))); 

另外你还可以用subpartition template的方式指定： 

CREATE TABLE sales_range_hash( 

  s_productid  NUMBER, 

  s_saledate   DATE, 

  s_custid     NUMBER, 

  s_totalprice NUMBER) 

PARTITION BY RANGE (s_saledate) 

SUBPARTITION BY HASH (s_productid) 

SUBPARTITION TEMPLATE( 

SUBPARTITION sp1 TABLESPACE tbs1, 

SUBPARTITION sp2 TABLESPACE tbs2, 

SUBPARTITION sp3 TABLESPACE tbs3, 

SUBPARTITION sp4 TABLESPACE tbs4, 

SUBPARTITION sp5 TABLESPACE tbs5, 

SUBPARTITION sp6 TABLESPACE tbs6, 

SUBPARTITION sp7 TABLESPACE tbs7, 

SUBPARTITION sp8 TABLESPACE tbs8) 

(PARTITION sal99q1 VALUES LESS THAN (TO_DATE(&＃39;01-APR-1999&＃39;, &＃39;DD-MON-YYYY&＃39;)), 

   PARTITION sal99q2 VALUES LESS THAN (TO_DATE(&＃39;01-JUL-1999&＃39;, &＃39;DD-MON-YYYY&＃39;)), 

   PARTITION sal99q3 VALUES LESS THAN (TO_DATE(&＃39;01-OCT-1999&＃39;, &＃39;DD-MON-YYYY&＃39;)), 

   PARTITION sal99q4 VALUES LESS THAN (TO_DATE(&＃39;01-JAN-2000&＃39;, &＃39;DD-MON-YYYY&＃39;))); 

这样，没有子分区通过的HASH分区将会统一到不同的表空间。 

 

二：使用各种分区方法的场景 

1：什么时候用Range Partition 

Range Partition是一种方便的方法分区历史的数据，经常在DATE COLMUN通过时间间隔组织数据。比如说：你要查询2009年8月的数据，查询将直接找到2009年8月的分区，避免了大量不必要的数据扫描。 

在处理周期性的load新数据和purge老数据的时候，Range Partition也是一个理想的选择。 

应用场景： 

   a)有一个大表需要通过时间字段频繁的访问，通过这个时间字段做RANG PARTITION 有利于做分区裁剪。 

   b）如果你不能对一个大表在指定的时间内做备份或RESTORE,你可以通过RANGE把他们分成小的logic片来做。

 

2：什么时候用HASH Partition 

HASH Partition不是一个很好的管理历史的方法。 

应用场景 

   a）增加大表的可用性。 

   b）避免各个分区之间查找数据，并且各个分区可以放在不同的设备上，达到最大的I0吞吐量。也可以用STORE IN 子句分配每个分区到不同的表空间。 

 

3：什么时候用LIST Partition 

如果你想映射数据到离散的值的时候，LIST Partition是个比较好的选择。 

 

4：什么时候用Composite Range-Hash Partitioning 

这是Range和Hash的组合使用，先对表用RANGE分，然后对每个RANGE再做HASH分区。 

由于做了RANGE后的子分区是没有规律的，如果在数据仓库设计时候，通过查询需求觉得有必要再细分，可以考虑使用。ORACLE会把子分区又分成不同的SEGMENT