Oracle11新特性：分区功能增强-Oracle新增复合分区 (转载)

Oracle11g在分区方面做了很大的提高，不但新增了4种复合分区类型，还增加了虚拟列分区、系统分区、INTERVAL分区等功能。 

    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行。