Oracle 组合分区（转）

Oracle 11g以前：Rang - List,  Rang - Hash

Oracle 11g：add Rang - Rang, List - List, List - Range, List - Hash

简要测试了range,hash,list分区的语法及维护操作;
 oracle也提供了组合分区的功能,详细资料请参考:http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#i2201774
 大致分为:
     组合范围分区
     组合列表分区
     组合哈希分区
     
 本文我们主要测试组合范围分区,奉上实例     
 
 ----测试情景:
   SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

 --oracle官方说,组合范围分区子分区可以是range,list,hash
 
 --先测试range类型的子分区
 
 ---子分区仍为range
 SQL> create table t_partition(a int,b int)
  2  partition by range(a)
  3  subpartition by range(b)
  4  subpartition template
  5  (
  6   subpartition sub1 values less than(5),
  7   subpartition sub2 values less than(10)
  8  )
  9  (
 10   partition p1 values less than (10),
 11   partition p2 values less than (20),
 12   partition p3 values less than (30)
 13  )
 14  /
 
Table created

---查询组合分区子分区键信息
SQL> select name,object_type,column_name,column_position from user_subpart_key_columns;
 
NAME                           OBJECT_TYPE COLUMN_NAME                                                                      COLUMN_POSITION
------------------------------ ----------- -------------------------------------------------------------------------------- ---------------
T_PARTITION                    TABLE       B                                                                                              1
 

---组合分区之子分区模块的相关信息 
SQL> select table_name,subpartition_name,subpartition_position,tablespace_name,high_bound from user_subpartition_templates;
 
TABLE_NAME                     SUBPARTITION_NAME                  SUBPARTITION_POSITION TABLESPACE_NAME                HIGH_BOUND
------------------------------ ---------------------------------- --------------------- ------------------------------ --------------------------------------------------------------------------------
T_PARTITION                    SUB1                                                   1                                5
T_PARTITION                    SUB2                                                   2                                10
  
---组合分区之子分区柱状图信息 
SQL> select table_name,subpartition_name,column_name,bucket_number,endpoint_value,endpoint_actual_value from user_subpart_histograms;
 
TABLE_NAME                     SUBPARTITION_NAME              COLUMN_NAME                                                                      BUCKET_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------- -------------- --------------------------------------------------------------------------------
 
SQL>

---组合分区子分区相关信息,类似于分区表user_tab_partitions
SQL> select table_name,partition_name,subpartition_name,high_value,tablespace_name from user_tab_subpartitions;
 
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              HIGH_VALUE                                                                       TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
T_PARTITION                    P1                             P1_SUB1                        5                                                                                TBS_HANG
T_PARTITION                    P1                             P1_SUB2                        10                                                                               TBS_HANG
T_PARTITION                    P2                             P2_SUB1                        5                                                                                TBS_HANG
T_PARTITION                    P2                             P2_SUB2                        10                                                                               TBS_HANG
T_PARTITION                    P3                             P3_SUB1                        5                                                                                TBS_HANG
T_PARTITION                    P3                             P3_SUB2                        10                                                                               TBS_HANG
 
6 rows selected

---组合分区之子分区类型为list
SQL> create table t_partition(a int,b int)
  2  partition by range(a)
  3  subpartition by list(b)
  4  subpartition template
  5  (
  6   subpartition sub1 values (1,2,3,4,5),
  7   subpartition sub2 values (6,7,8,9,10)
  8  )
  9  (
 10   partition p1 values less than (10),
 11   partition p2 values less than (20),
 12   partition p3 values less than (30)
 13  )
 14  /
 
Table created

---组合分区之子分区类型hash

---创建子分区类型hash方法一
SQL> create table t_partition(a int,b int)
  2  partition by range(a)
  3  subpartition by hash(b)
  4  subpartitions 2
  5  (
  6   partition p1 values less than (10),
  7   partition p2 values less than (20),
  8   partition p3 values less than (30)
  9  )
 10  /
 
Table created 

---创建子分区类型hash方法二
SQL> create table t_partition(a int,b int)
  2  partition by range(a)
  3  subpartition by hash(b)
  4  subpartition template
  5  (subpartition sub1,
  6   subpartition sub2
  7   )
  8  (
  9   partition p1 values less than (10),
 10   partition p2 values less than (20),
 11   partition p3 values less than (30)
 12  )
 13  /
 
Table created

---创建子分区类型hash方法方法三(注:子分区可混合并用subpartition和subpartition)
SQL> create table t_partition(a int,b int)
  2  partition by range(a)
  3  subpartition by hash(b)
  4  (
  5   partition p1 values less than (10)
  6    subpartitions 2,
  7   partition p2 values less than (20)
  8    (subpartition subp1,
  9     subpartition subp2),
 10   partition p3 values less than (30)
 11  )
 12  /
 
Table created

 

---用子分区模板创建组合分区(模板:可理解为通用方法,一次创建多次使用)
---范围子分区
SQL> create table t_hash(a int,b int)
  2  partition by hash(a)
  3  subpartition by range(b)
  4  subpartition template(
  5                        subpartition sub1 values less than (5),
  6                        subpartition sub2 values less than (10)
  7                       )
  8  (
  9   partition px1,
 10   partition px2
 11  )
 12  /
 
Table created

---列表子分区
SQL> create table t_hash(a int,b int)
  2  partition by hash(a)
  3  subpartition by list(b)
  4  subpartition template(
  5                        subpartition sub1 values (1,2,3,4,5),
  6                        subpartition sub2 values (6,7,8,9,10)
  7                       )
  8  (
  9   partition px1,
 10   partition px2
 11  )
 12  /
 
Table created

 

---哈希子分区
SQL> create table t_hash(a int,b int)
  2  partition by hash(a)
  3  subpartition by hash(b)
  4  subpartition template(
  5                        subpartition sub1,
  6                        subpartition sub2
  7                       )
  8  (
  9   partition px1,
 10   partition px2
 11  )
 12  /
 
Table created

 

表分区的知识要点,承沿之前系列,测试列表组合分区

---范围子分区
SQL> create table t_list(a int,b int)
  2  partition by list(a)
  3  subpartition by range(b)
  4  subpartition template
  5  (
  6   subpartition sub1 values less than(5),
  7   subpartition sub2 values less than(10)
  8   )
  9  (
 10   partition p1 values(1,2,3,4,5),
 11   partition p2 values(6,7,8,9,10)
 12  )
 13  /
 
Table created          
   
          
---列表子分区
SQL> create table t_list(a int,b int)
  2  partition by list(a)
  3  subpartition by list(b)
  4  subpartition template
  5  (
  6   subpartition sub1 values(1,2),
  7   subpartition sub2 values(3,4)
  8   )
  9  (
 10   partition p1 values(1,2,3,4,5),
 11   partition p2 values(6,7,8,9,10)
 12  )
 13  /
 
Table created   

---哈希子分区方法一
SQL> create table t_list(a int,b int)
  2  partition by list(a)
  3  subpartition by hash(b)
  4  subpartition template
  5  (
  6   subpartition sub1,
  7   subpartition sub2
  8   )
  9  (
 10   partition p1 values(1,2,3,4,5),
 11   partition p2 values(6,7,8,9,10)
 12  )
 13  /
 
Table created  

---哈希子分区方法二
SQL> create table t_list(a int,b int)
  2  partition by list(a)
  3  subpartition by hash(b)
  4    subpartitions 2
  5  (
  6   partition p1 values(1,2,3,4,5),
  7   partition p2 values(6,7,8,9,10)
  8  )
  9  /
 
Table created

 

转自：http://blog.itpub.net/9240380/viewspace-752479/