oracle分区效果,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行。