（转）Oracle分区表 (Partition Table) 的创建及管理
Oracle分区表 (Partition Table) 的创建及管理
?
一、创建分区表
分区表分为四类：1、范围分区表 2、列表分区表 3、哈希分区表 4、组合分区表
下面分别创建四类分区表。
1、范围分区表
CREATE TABLE range_example(
???? ?range_key_column DATE,
????? DATA VARCHAR2(20),
????? ID integer
) PARTITION BY RANGE(range_key_column)
(
? PARTITION part01 VALUES LESS THAN (TO_DATE('2008-07-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs01,
??PARTITION part02 VALUES LESS THAN (TO_DATE('2008-08-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs02,
? PARTITION part03 VALUES LESS THAN (TO_DATE('2008-09-1 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE tbs03
);
2、列表分区表
CREATE TABLE list_example(
????? dname VARCHAR2(10),????? DATA VARCHAR2(20)
) PARTITION BY LIST(dname)
(????
? PARTITION part01 VALUES('ME','PE','QC','RD'),
? PARTITION part02 VALUES('SMT','SALE')
);
3、哈希分区表
CREATE TABLE hash_example(
????? hash_key_column DATE,
????? DATA VARCHAR2(20)
) PARTITION BY HASH(hash_key_cloumn)
(????
??PARTITION part01 ,
??PARTITION part02
);
4、组合分区表
CREATE TABLE range_hash_example(
????? range_column_key DATE,
????? hash_column_key INT,
???? ?DATA VARCHAR2(20)
)
PARTITION BY RANGE(range_column_key)
SUBPARTITION BY HASH(hash_column_key) SUBPARTITIONS 2
(
?? PARTITION part_1 VALUES LESS THAN (TO_DATE('2008-08-01','yyyy-mm-dd'))(
?
??????? SUBPARTITION part_1_sub_1,??????? SUBPARTITION part_1_sub_2,
??????? SUBPARTITION part_1_sub_3
?? ),
?? PARTITION part_2 VALUES LESS THAN (TO_DATE('2008-09-01','yyyy-mm-dd'))(
??????? SUBPARTITION part_2_sub_1,
? ????? SUBPARTITION part_2_sub_2
