Oracle11新特性——分区功能增强（一）

介绍Oracle新增的4种复合分区。




从9i开始，Oracle就包括了2种复合分区，RANGE-HASH和RANGE-LIST。在11g，Oracle一下就提供了4种复合分区：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST。

SQL> CREATE TABLE T_RANGE_RANGE
2 PARTITION BY RANGE (CREATED)
3 SUBPARTITION BY RANGE (LAST_DDL_TIME)
4 (
5 PARTITION P1 VALUES LESS THAN (TO_DATE('2007-9-1', 'YYYY-MM-DD'))
6 (
7 SUBPARTITION P1_SP1 VALUES LESS THAN (TO_DATE('2007-9-1', 'YYYY-MM-DD')),
8 SUBPARTITION P1_SP2 VALUES LESS THAN (TO_DATE('2007-10-1', 'YYYY-MM-DD')),
9 SUBPARTITION P1_SP3 VALUES LESS THAN (MAXVALUE)
10 ),
11 PARTITION P2 VALUES LESS THAN (TO_DATE('2007-10-1', 'YYYY-MM-DD'))
12 (
13 SUBPARTITION P2_SP1 VALUES LESS THAN (TO_DATE('2007-9-1', 'YYYY-MM-DD')),
14 SUBPARTITION P2_SP2 VALUES LESS THAN (TO_DATE('2007-10-1', 'YYYY-MM-DD')),
15 SUBPARTITION P2_SP3 VALUES LESS THAN (MAXVALUE)
16 ),
17 PARTITION P3 VALUES LESS THAN (MAXVALUE)
18 (
19 SUBPARTITION P3_SP1 VALUES LESS THAN (TO_DATE('2007-9-1', 'YYYY-MM-DD')),
20 SUBPARTITION P3_SP2 VALUES LESS THAN (TO_DATE('2007-10-1', 'YYYY-MM-DD')),
21 SUBPARTITION P3_SP3 VALUES LESS THAN (MAXVALUE)
22 )
23 )
24 AS SELECT * FROM DBA_OBJECTS;

表已创建。

在没有RANGE-RANGE复合分区之前，RANGE分区指定两个分区列可以达到类似的效果。不过采用RANGE-RANGE分区逻辑上也清晰得多，而且可以利用很多处理子分区功能。

SQL> CREATE TABLE T_LIST_RANGE
2 PARTITION BY LIST (OWNER)
3 SUBPARTITION BY RANGE (CREATED)
4 (
5 PARTITION P1 VALUES ('SYS', 'SYSTEM')
6 (
7 SUBPARTITION P1_SP1 VALUES LESS THAN (TO_DATE('2007-9-1', 'YYYY-MM-DD')),
8 SUBPARTITION P1_SP2 VALUES LESS THAN (TO_DATE('2007-10-1', 'YYYY-MM-DD')),
9 SUBPARTITION P1_SP3 VALUES LESS THAN (MAXVALUE)
10 ),
11 PARTITION P2 VALUES ('YANGTK')
12 (
13 SUBPARTITION P2_SP1 VALUES LESS THAN (TO_DATE('2007-9-1', 'YYYY-MM-DD')),
14 SUBPARTITION P2_SP2 VALUES LESS THAN (TO_DATE('2007-10-1', 'YYYY-MM-DD')),
15 SUBPARTITION P2_SP3 VALUES LESS THAN (MAXVALUE)
16 ),
17 PARTITION P3 VALUES (DEFAULT)
18 (
19 SUBPARTITION P3_SP1 VALUES LESS THAN (TO_DATE('2007-9-1', 'YYYY-MM-DD')),
20 SUBPARTITION P3_SP2 VALUES LESS THAN (TO_DATE('2007-10-1', 'YYYY-MM-DD')),
21 SUBPARTITION P3_SP3 VALUES LESS THAN (MAXVALUE)
22 )
23 )
24 AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE TABLE T_LIST_HASH
2 PARTITION BY LIST (OWNER)
3 SUBPARTITION BY HASH (OBJECT_ID)
4 SUBPARTITION TEMPLATE
5 (
6 SUBPARTITION SP1,
7 SUBPARTITION SP2,
8 SUBPARTITION SP3,
9 SUBPARTITION SP4
10 )
11 (
12 PARTITION P1 VALUES ('SYS', 'SYSTEM'),
13 PARTITION P2 VALUES ('YANGTK'),
14 PARTITION P3 VALUES (DEFAULT)
15 )
16 AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE TABLE T_LIST_LIST
2 PARTITION BY LIST (OWNER)
3 SUBPARTITION BY LIST (OBJECT_TYPE)
4 (
5 PARTITION P1 VALUES ('SYS', 'SYSTEM')
6 (
7 SUBPARTITION P1_SP1 VALUES ('TABLE', 'INDEX'),
8 SUBPARTITION P1_SP2 VALUES ('VIEW', 'SYNONYM'),
9 SUBPARTITION P1_SP3 VALUES (DEFAULT)
10 ),
11 PARTITION P2 VALUES ('YANGTK')
12 (
13 SUBPARTITION P2_SP1 VALUES ('TABLE', 'INDEX'),
14 SUBPARTITION P2_SP2 VALUES ('VIEW', 'SYNONYM'),
15 SUBPARTITION P2_SP3 VALUES (DEFAULT)
16 ),
17 PARTITION P3 VALUES (DEFAULT)
18 (
19 SUBPARTITION P3_SP1 VALUES ('TABLE', 'INDEX'),
20 SUBPARTITION P3_SP2 VALUES ('VIEW', 'SYNONYM'),
21 SUBPARTITION P3_SP3 VALUES (DEFAULT)
22 )
23 )
24 AS SELECT * FROM DBA_OBJECTS;

表已创建。

三种LIST开头的复合分区的加入，使得Oracle支持除HASH开头之外的全部6种复合分区。这使得在设计复合分区的时候有更多的选择可供使用。

SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT
2 FROM USER_PART_TABLES;

TABLE_NAME PARTITION SUBPART PARTITION_COUNT
------------------------------ --------- ------- ---------------
T_LIST_HASH LIST HASH 3
T_LIST_LIST LIST LIST 3
T_LIST_RANGE LIST RANGE 3
T_RANGE_RANGE RANGE RANGE 3

SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS
2 ORDER BY 1, 2, 3;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ -----------------
T_LIST_HASH P1 P1_SP1
T_LIST_HASH P1 P1_SP2
T_LIST_HASH P1 P1_SP3
T_LIST_HASH P1 P1_SP4
T_LIST_HASH P2 P2_SP1
T_LIST_HASH P2 P2_SP2
T_LIST_HASH P2 P2_SP3
T_LIST_HASH P2 P2_SP4
T_LIST_HASH P3 P3_SP1
T_LIST_HASH P3 P3_SP2
T_LIST_HASH P3 P3_SP3
T_LIST_HASH P3 P3_SP4
T_LIST_LIST P1 P1_SP1
T_LIST_LIST P1 P1_SP2
T_LIST_LIST P1 P1_SP3
T_LIST_LIST P2 P2_SP1
T_LIST_LIST P2 P2_SP2
T_LIST_LIST P2 P2_SP3
T_LIST_LIST P3 P3_SP1
T_LIST_LIST P3 P3_SP2
T_LIST_LIST P3 P3_SP3
T_LIST_RANGE P1 P1_SP1
T_LIST_RANGE P1 P1_SP2
T_LIST_RANGE P1 P1_SP3
T_LIST_RANGE P2 P2_SP1
T_LIST_RANGE P2 P2_SP2
T_LIST_RANGE P2 P2_SP3
T_LIST_RANGE P3 P3_SP1
T_LIST_RANGE P3 P3_SP2
T_LIST_RANGE P3 P3_SP3
T_RANGE_RANGE P1 P1_SP1
T_RANGE_RANGE P1 P1_SP2
T_RANGE_RANGE P1 P1_SP3
T_RANGE_RANGE P2 P2_SP1
T_RANGE_RANGE P2 P2_SP2
T_RANGE_RANGE P2 P2_SP3
T_RANGE_RANGE P3 P3_SP1
T_RANGE_RANGE P3 P3_SP2
T_RANGE_RANGE P3 P3_SP3