Choose the appropriate partition method (range, hash, list, and composite)

ORACLE的分区方法有，范围分区，HASH分区，列表分区，还有复合分区。
范围分区通常用于基于时间的分区，它可以按某种值的范围递增，并且可以设置maxvalue值。
LIST分区通常用于，按地区，有一明确值的内容范围的列的分区，每个分区都用一个明确值确定。
HASH分区使用hash算法进行分区，只需定义分区数量即可，常用于需要把某一个字段值打散的情况。
复合分区只有2种，范围-列表分区，范围-hash分区，父分区都是范围分区，子分区可以是列表或hash。
 
1.范围分区：
create table part_tab1(
id number(20),
name varchar2(30),
location varchar(20),
ttime date,
ecode number(15)
)
partition by range (ttime)
(
partition p1 values less than (to_date('2012-06-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2012-07-01','yyyy-mm-dd')),
partition p3 values less than (to_date('2012-08-01','yyyy-mm-dd')),
partition p4 values less than (to_date('2012-09-01','yyyy-mm-dd')),
partition p5 values less than (to_date('2012-10-01','yyyy-mm-dd')),
partition p6 values less than (to_date('2012-11-01','yyyy-mm-dd')),
partition p7 values less than (to_date('2012-12-01','yyyy-mm-dd')),
partition pmax values less than (maxvalue)
);
 
2.列表分区：
create table part_tab2(
id number(20),
name varchar2(30),
location varchar(20),
ttime date,
ecode number(15)
)
partition by list (location)
(
partition psz values ('SHENZHEN'),
partition pgz values ('GUANGZHOU'),
partition pnj values ('NANJING'),
partition psh values ('SHANGHAI'),
partition pbj values ('BEIJING')
);
 
3.哈希分区：
create table part_tab3(
id number(20),
name varchar2(30),
location varchar(20),
ttime date,
ecode number(15)
)
PARTITION BY HASH (ecode)
PARTITIONS 5
STORE IN (LDYTBS, TRANS2, USERS);
 
create table sale_hash
(id number,
name varchar2(20)
)
partition by hash(id)
(
partition p1 tablespace data01,
partition p2 tablespace data02,
partition p3 tablespace data03,
partition p4 tablespace data04,
partition p5 tablespace data05
);
 
4.范围-列表分区：
因为把子分区写在前面，这里使用了SUBPARTITION TEMPLATE，作为子分区模板。
create table part_tab5(
id number(20),
name varchar2(30),
location varchar(20),
ttime date,
ecode number(15)
)
partition by range (ttime)
SUBPARTITION BY LIST (location)
SUBPARTITION TEMPLATE
(
SUBPARTITION psz values ('SHENZHEN'),
SUBPARTITION pgz values ('GUANGZHOU'),
SUBPARTITION pnj values ('NANJING'),
SUBPARTITION psh values ('SHANGHAI'),
SUBPARTITION pbj values ('BEIJING')
)
(
partition p1 values less than (to_date('2012-06-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2012-07-01','yyyy-mm-dd')),
partition p3 values less than (to_date('2012-08-01','yyyy-mm-dd')),
partition p4 values less than (to_date('2012-09-01','yyyy-mm-dd')),
partition p5 values less than (to_date('2012-10-01','yyyy-mm-dd')),
partition p6 values less than (to_date('2012-11-01','yyyy-mm-dd')),
partition p7 values less than (to_date('2012-12-01','yyyy-mm-dd')),
partition pmax values less than (maxvalue)
);
 
5.范围-哈希分区：
create table part_tab4(
id number(20),
name varchar2(30),
location varchar(20),
ttime date,
ecode number(15)
)
partition by range (ttime)
SUBPARTITION BY HASH (ecode)
SUBPARTITIONS 4
(
partition p1 values less than (to_date('2012-06-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2012-07-01','yyyy-mm-dd')),
partition p3 values less than (to_date('2012-08-01','yyyy-mm-dd')),
partition p4 values less than (to_date('2012-09-01','yyyy-mm-dd')),
partition p5 values less than (to_date('2012-10-01','yyyy-mm-dd')),
partition p6 values less than (to_date('2012-11-01','yyyy-mm-dd')),
partition p7 values less than (to_date('2012-12-01','yyyy-mm-dd')),
partition pmax values less than (maxvalue)
)
;