oracle composite partition組合分割槽_composite partition range

 前幾篇文章:http://space.itpub.net/index.php?uid/9240380/action/spacelist/php/1,
簡要測試了range,hash,list分割槽的語法及維護操作;
 oracle也提供了組合分割槽的功能,詳細資料請參考:
 大致分為:
     組合範圍分割槽
     組合列表分割槽
     組合雜湊分割槽
    
 本文我們主要測試組合範圍分割槽,奉上例項    
 
 ----測試情景:
   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