Oracle11新特性——分割槽功能增強（一）

打算寫一系列的文章介紹11g的新特性和變化。

Oracle11g在分割槽方面做了很大的提高，不但新增了4種複合分割槽型別，還增加了虛擬列分割槽、系統分割槽、INTERVAL分割槽等功能。

這一篇介紹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

已選擇39行。