ORACLE分区表

ORACLE分区表
一什么是分区表
分区表就是将一张“大表”按照一定的条件分成多个区，各个区建议存放在不同的表空间中，这样可以提高可用性。

二分区类型
分区表共有四种类型分别为：
Range Partitioning–区间分区
Hash Partitioning-散列分区
List Partitioning-列表分区
Composite Partitioning-组合分区
三分区类型举例：
3.1Range Partitioning–区间分区
区间分区示例创建语句如下：
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('02/01/2000','DD/MM/YYYY')) tablespace p1,
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))tablespace
p2,
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) tablespace p3,
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) tablespace p4,
PARTITION SALES_MAX VALUES LESS THAN (MAXVALUE) TABLESPACE P4);
分区键:分区表引入了分区键以便于根据某个区间值将表进行分区，上边示例中的sales_date就是区间分区的分区键。

这条创建语句将表sales_range按照SALES_data字段分成了sales_jan2000、sales_feb2000、sales_mar2000、sales_apr2000四个分区。

当我们插入数据的时候如果SALES_DATE字段小于2000-1-2日就将此条数据放在sales_jan2000区间中，小于2000-1-3就放在sales_feb2000区间中。

那么如果我们插入的数据超过了上届怎么办呢？请注意最后一行也就是SALES_MAX分区，这个分区记录着超过2000-1-5的所有数据。

同时我们可以看到每个分区后的TABLESPACE子句，它用于指定此分区位于哪个表空间。

3.2Hash Partitioning-散列分区
ORACLE会随机的根据分区键值将数据平均的分配到表的分区中区，但是分区数必须是2的幂数
CREATE TABLE sales_hash2
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(salesman_id)
(PARTITION p1,
PARTITION p2);
这样当我们插入数据的时候，数据会均匀随机的被分配到P1和p2两个分区中
3.3List Partitioning-列表分区
列表分区提供了这样一种功能，你可以在分区上定义分区键的具体值，这样当你插入数据的时候系统就能根据分区键列来决定此条数据被分配到哪个分区
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE) COMPRESS
PARTITION BY LIST(sales_state)
(PARTITION sales_west VALUES('California', 'Hawaii'), PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'), PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT));
这是列表分区的一个典型例子，当我们INSERT 时若SALES_STATE的值为California或者Hawaii此条数据就会被分配到SALES_WEST分区；若SALES_STATE为'New York'、'Virginia'或者'Florida'就会被分配到SALES_EAST。

同时我们注意到有一个COMPRESS参数，这个参数表示我想尽可能的压缩这个表，这样这个表就能存储更过的数据同时I/O效率也更高，但是这样的代价是你INSERT数据时的时间会加倍。

COMPRESS参数也可针对某一个分区压缩：
CREATE TABLE sales_list2
(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('California', 'Hawaii') compress, PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'), PARTITION sales_central VALUES('Texas', 'Illinois'), PARTITION sales_other VALUES(DEFAULT));
3.4组合分区：
3.4.1范围列表分区
这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区，子分区用SUBPARTITION子句标识。

CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE p1
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE p1,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE p1
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE p1
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE p1,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE p1
)
);
3.4.2复合范围散列分区：
这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

create table dinya_test
(
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date
)
partition by range(transaction_date) subpartition by hash(transaction_id) subpartitions 2 --表示每个主分区下有两个子分区
(partition part_01 values less
than(to_date('2006-01-01','yyyy-mm-dd'))
(subpartition part_1_sun_1,
subpartition part_1_sun_2),
partition part_02 values less
than(to_date('2010-01-01','yyyy-mm-dd'))
(subpartition part_1_sun_3,
subpartition part_1_sun_4),
partition part_03 values less than(maxvalue) (subpartition part_1_sun_5,
subpartition part_1_sun_6)
);
也可以这样写用以省略SUBPARTITION分区名
create table dinya_test2
(
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date
)
partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 2 store in (p1,p2,p3) –虽然省略了SUBPARTITION名但是要指定SUBPARTITION所存储的表空间名
(
partition part_01 values less than(to_date('2006-01-1','yyyy-mm-dd')),
partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')),
partition part_03 values less than(maxvalue)
);。