分区表中subpartition的使用方法

在Oracle分区中，有一个subpartition 二级分区的用法。一般用在组合分区里。先按照range分区，每个子分区又按照list or hash分区。
范围哈希组合分区：
create table emp (
empno number(4),
ename varchar2(30),
hiredate date)
partition by range (hiredate)
subpartition by hash (empno)
subpartitions 2
(partition e1 values less than (to_date('20020501','YYYYMMDD')),
partition e2 values less than (to_date('20021001','YYYYMMDD')),
partition e3 values less than (maxvalue));

CREATE TABLE customers_part (
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
nls_territory VARCHAR2(30),
credit_limit NUMBER(9,2))
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION TEMPLATE
(SUBPARTITION east VALUES ('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
SUBPARTITION west VALUES ('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));


进行删除和增加分区与操作rang分区一样。
增加一个分区
alter TABLE customers_part ADD PARTITION sales2000_q1
VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’)
( SUBPARTITION sales2000_q1_sub_par1 VALUES ('01'),
SUBPARTITION sales2000_q1_sub_par2 VALUES ('02')
);

删除一个分区 alter table customers_part drop partition sales2000_q1;