Oracle分区表

1、范围分区（range）

create table RANGE_PART_TAB
(
  id        NUMBER,
  deal_date DATE,
  area_code NUMBER,
  contents  VARCHAR2(4000)
)
partition by range (DEAL_DATE)
(
  partition P201201 values less than (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  partition P201202 values less than (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  partition P201203 values less than (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))   
  partition P_MAX values less than (MAXVALUE)
);

2、哈希分区（hash）

CREATE TABLE hash_part_tab(ID NUMBER,deal_date DATE,area_code NUMBER,CONTENTS VARCHAR2(4000))
PARTITION BY HASH(deal_date)
PARTITIONS 12
;

3、列表分区（list）

CREATE TABLE list_part_tab(ID NUMBER,deal_date DATE,area_code NUMBER,CONTENTS VARCHAR2(4000))
PARTITION BY LIST(area_code)
(
PARTITION p_591 VALUES(591),
PARTITION p_592 VALUES(592),
PARTITION p_593 VALUES(593),
PARTITION p_594 VALUES(594),
PARTITION p_595 VALUES(595),
PARTITION p_596 VALUES(596),
PARTITION p_597 VALUES(597),
PARTITION p_598 VALUES(598),
PARTITION p_599 VALUES(599),
PARTITION p_other VALUES(DEFAULT)
);

4、组合分区

比如Oracle 11g以前有范围-哈希组合分区、范围-列表组合分区；oracle 11g，还提供了range-range、list-range、list-hash、list-list这4种。

CREATE TABLE range_list_part_tab(ID NUMBER,deal_date DATE,area_code NUMBER,CONTENTS VARCHAR2(4000))
PARTITION BY RANGE(deal_date)
  SUBPARTITION BY LIST(area_code)
  SUBPARTITION TEMPLATE
  (
SUBPARTITION p_591 VALUES(591),
SUBPARTITION p_592 VALUES(592),
SUBPARTITION p_593 VALUES(593),
SUBPARTITION p_594 VALUES(594),
SUBPARTITION p_595 VALUES(595),
SUBPARTITION p_596 VALUES(596),
SUBPARTITION p_597 VALUES(597),
SUBPARTITION p_598 VALUES(598),
SUBPARTITION p_599 VALUES(599),
SUBPARTITION p_other VALUES(DEFAULT)
)
(
PARTITION p1 VALUES LESS THAN(to_date('2012-02-01','YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN(to_date('2012-03-01','YYYY-MM-DD')),
PARTITION p3 VALUES LESS THAN(to_date('2012-04-01','YYYY-MM-DD')),
PARTITION p4 VALUES LESS THAN(to_date('2012-05-01','YYYY-MM-DD')),
PARTITION p5 VALUES LESS THAN(to_date('2012-06-01','YYYY-MM-DD')),
PARTITION p6 VALUES LESS THAN(to_date('2012-07-01','YYYY-MM-DD')),
PARTITION p7 VALUES LESS THAN(to_date('2012-08-01','YYYY-MM-DD')),
PARTITION p8 VALUES LESS THAN(to_date('2012-09-01','YYYY-MM-DD')),
PARTITION p9 VALUES LESS THAN(to_date('2012-10-01','YYYY-MM-DD')),
PARTITION p10 VALUES LESS THAN(to_date('2012-11-01','YYYY-MM-DD')),
PARTITION p11 VALUES LESS THAN(to_date('2012-12-01','YYYY-MM-DD')),
PARTITION p12 VALUES LESS THAN(to_date('2013-01-01','YYYY-MM-DD')),
PARTITION p_max VALUES LESS THAN(MAXVALUE)
);
5、对分区表的操作

SELECT * FROM range_part_tab PARTITION(P_MAX);
ALTER TABLE range_part_tab DROP PARTITION P_MAX;
ALTER TABLE range_part_tab ADD PARTITION P201303 values less than (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
ALTER TABLE range_part_tab ADD PARTITION P_MAX values less than (MAXVALUE);
ALTER TABLE range_part_tab TRUNCATE PARTITION P_MAX;

--分区交换，可以实现普通表和分区表的某个分区之间数据的相互交换，执行一次交换一次。这两个表字段必须是完全一样。
CREATE TABLE mid_table AS
SELECT * FROM range_part_tab WHERE 1=2;
ALTER TABLE Range_Part_Tab EXCHANGE PARTITION P201303 WITH TABLE mid_table;

--分区切割
ALTER TABLE Range_Part_Tab SPLIT PARTITION P_MAX AT (to_date('2013-05-01','YYYY-MM-DD')) INTO (PARTITION P201304,PARTITION P_MAX);

--分区合并
ALTER TABLE range_part_tab MERGE PARTITIONS P201304,P_MAX INTO PARTITION P_MAX;