中复合分区的类型大大增加,现在 Range,List,Interval 都可以作为 Top level
分区, 而 Second level 则可以是 Range, List, Hash, 也就是在 11g 中可以有 3*3=9
种复合分区,满足更多的业务需求
一:RANGE-RANGE
建表示例:
CREATE TABLE TEST_RANGE_RANGE (vl1 date,vl2 number(12))
PARTITION BY RANGE (vl1)
SUBPARTITION BY RANGE (vl2)
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2011-9-1', 'YYYY-MM-DD'))
(
SUBPARTITION P1_1 VALUES LESS THAN (100000),
SUBPARTITION P1_2 VALUES LESS THAN (200000),
SUBPARTITION P1_3 VALUES LESS THAN (MAXVALUE)
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2011-10-1', 'YYYY-MM-DD'))
(
SUBPARTITION P2_1 VALUES LESS THAN (100000),
SUBPARTITION P2_2 VALUES LESS THAN (200000),
SUBPARTITION P2_3 VALUES LESS THAN (MAXVALUE)
),
PARTITION P3 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION P3_1 VALUES LESS THAN (100000),
SUBPARTITION P3_2 VALUES LESS THAN (200000),
SUBPARTITION P3_3 VALUES LESS THAN (MAXVALUE)
)
);
二:LIST-RANGE
建表示例:
CREATE TABLE TEST_LIST_RANGE (vl1 varchar2(20),vl2 number(12))
PARTITION BY LIST (vl1)
SUBPARTITION BY RANGE (vl2)
(
PARTITION P1 VALUES ('MIN', 'HOUR','SECOND')
(
SUBPARTITION P1_1 VALUES LESS THAN (100000),
SUBPARTITION P1_2 VALUES LESS THAN (200000),
SUBPARTITION P1_3 VALUES LESS THAN (MAXVALUE)
),
PARTITION P2 VALUES ('DAY', 'MONTH','YEAR')
(
SUBPARTITION P2_1 VALUES LESS THAN (100000),
SUBPARTITION P2_2 VALUES LESS THAN (200000),
SUBPARTITION P2_3 VALUES LESS THAN (MAXVALUE)
),
PARTITION P3 VALUES (DEFAULT)
(
SUBPARTITION P3_1 VALUES LESS THAN (100000),
SUBPARTITION P3_2 VALUES LESS THAN (200000),
SUBPARTITION P3_3 VALUES LESS THAN (MAXVALUE)
)
);
三:LIST-HASH
建表示例:
CREATE TABLE TEST_LIST_HASH (vl1 varchar2(20),vl2 number(12))
PARTITION BY LIST (vl1)
SUBPARTITION BY HASH (vl2)
SUBPARTITION TEMPLATE
(
SUBPARTITION SP1,
SUBPARTITION SP2,
SUBPARTITION SP3,
SUBPARTITION SP4
)
(
PARTITION P1 VALUES ('MIN', 'HOUR','SECOND'),
PARTITION P2 VALUES ('DAY', 'MONTH','YEAR'),
PARTITION P3 VALUES (DEFAULT)
)