创建range-hash（范围-哈希）组合分区！

来源：互联网发布：wpf 管理系统源码编辑：程序博客网时间：2024/05/05 18:00

--创建range-hash组合分区：

SQL> create table t_partition_rh (id number,name varchar2(50))  2  partition by range(id) subpartition by hash(name)  3  subpartitions 4 store in (tbspart01, tbspart02, tbspart03,tbspart04)(  4  partition t_r_p1 values less than (10) tablespace tbspart01,  5  partition t_r_p2 values less than (20) tablespace tbspart02,  6  partition t_r_p3 values less than (30) tablespace tbspart03,  7  partition t_r_pd values less than (maxvalue) tablespace tbspart04);表已创建。SQL> select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT from user_part_tables where TABLE_NAME = 'T_PARTITION_RH';TABLE_NAME                     PARTITI PARTITION_COUNT------------------------------ ------- ---------------T_PARTITION_RH                 RANGE                 4SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RH';PARTITION_NAME                 HIGH_VALUE           TABLESPACE_NAME------------------------------ -------------------- ------------------------------T_R_P1                         10                   TBSPART01T_R_P2                         20                   TBSPART02T_R_P3                         30                   TBSPART03T_R_PD                         MAXVALUE             TBSPART04SQL> select partition_name,subpartition_name,tablespace_name from user_tab_subpartitions where table_name='T_PARTITION_RH';PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------T_R_P1                         SYS_SUBP36                     TBSPART01T_R_P1                         SYS_SUBP35                     TBSPART01T_R_P1                         SYS_SUBP34                     TBSPART01T_R_P1                         SYS_SUBP33                     TBSPART01T_R_P2                         SYS_SUBP40                     TBSPART02T_R_P2                         SYS_SUBP39                     TBSPART02T_R_P2                         SYS_SUBP38                     TBSPART02T_R_P2                         SYS_SUBP37                     TBSPART02T_R_P3                         SYS_SUBP44                     TBSPART03T_R_P3                         SYS_SUBP43                     TBSPART03T_R_P3                         SYS_SUBP42                     TBSPART03PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------T_R_P3                         SYS_SUBP41                     TBSPART03T_R_PD                         SYS_SUBP48                     TBSPART04T_R_PD                         SYS_SUBP47                     TBSPART04T_R_PD                         SYS_SUBP46                     TBSPART04T_R_PD                         SYS_SUBP45                     TBSPART04已选择16行。

--对某个分区创建hash子分区

SQL> create table t_partition_rh (id number,name varchar2(50))  2  partition by range(id) subpartition by hash(name)(  3  partition t_r_p1 values less than (10) tablespace tbspart01,  4  partition t_r_p2 values less than (20) tablespace tbspart02,  5  partition t_r_p3 values less than (30) tablespace tbspart03  6  (subpartition t_r_p3_h1 tablespace tbspart01,  7   subpartition t_r_p3_h2 tablespace tbspart02,  8   subpartition t_r_p3_h3 tablespace tbspart03),  9  partition t_r_pd values less than (maxvalue) tablespace tbspart04);表已创建。SQL> select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT from user_part_tables where TABLE_NAME = 'T_PARTITION_RH';TABLE_NAME                     PARTITI PARTITION_COUNT------------------------------ ------- ---------------T_PARTITION_RH                 RANGE                 4SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RH';PARTITION_NAME                 HIGH_VALUE           TABLESPACE_NAME------------------------------ -------------------- ------------------------------T_R_P1                         10                   TBSPART01T_R_P2                         20                   TBSPART02T_R_P3                         30                   TBSPART03T_R_PD                         MAXVALUE             TBSPART04SQL> select partition_name,subpartition_name,tablespace_name from user_tab_subpartitions where table_name='T_PARTITION_RH';PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------T_R_P1                         SYS_SUBP49                     TBSPART01T_R_P2                         SYS_SUBP50                     TBSPART02T_R_P3                         T_R_P3_H3                      TBSPART03T_R_P3                         T_R_P3_H2                      TBSPART02T_R_P3                         T_R_P3_H1                      TBSPART01T_R_PD                         SYS_SUBP51                     TBSPART04已选择6行。

--还可以给各个分区指定不同的子分区：

SQL> create table t_partition_rh (id number,name varchar2(50))  2  partition by range(id) subpartition by hash(name)(  3  partition t_r_p1 values less than (10) tablespace tbspart01,  4  partition t_r_p2 values less than (20) tablespace tbspart02  5  (subpartition t_r_p2_h1 tablespace tbspart01,  6   subpartition t_r_p2_h2 tablespace tbspart02),  7  partition t_r_p3 values less than (30) tablespace tbspart03  8   subpartitions 3 store in (tbspart01,tbspart02,tbspart03),  9  partition t_r_pd values less than (maxvalue) tablespace tbspart04 10  (subpartition t_r_p3_h1 tablespace tbspart01, 11   subpartition t_r_p3_h2 tablespace tbspart02, 12   subpartition t_r_p3_h3 tablespace tbspart03));表已创建。SQL> select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT from user_part_tables where TABLE_NAME = 'T_PARTITION_RH';TABLE_NAME                     PARTITI PARTITION_COUNT------------------------------ ------- ---------------T_PARTITION_RH                 RANGE                 4SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RH';PARTITION_NAME                 HIGH_VALUE           TABLESPACE_NAME------------------------------ -------------------- ------------------------------T_R_P1                         10                   TBSPART01T_R_P2                         20                   TBSPART02T_R_P3                         30                   TBSPART03T_R_PD                         MAXVALUE             TBSPART04SQL> select partition_name,subpartition_name,tablespace_name from user_tab_subpartitions where table_name='T_PARTITION_RH';PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------T_R_P1                         SYS_SUBP52                     TBSPART01T_R_P2                         T_R_P2_H2                      TBSPART02T_R_P2                         T_R_P2_H1                      TBSPART01T_R_P3                         SYS_SUBP55                     TBSPART03T_R_P3                         SYS_SUBP54                     TBSPART02T_R_P3                         SYS_SUBP53                     TBSPART01T_R_PD                         T_R_P3_H3                      TBSPART03T_R_PD                         T_R_P3_H2                      TBSPART02T_R_PD                         T_R_P3_H1                      TBSPART01已选择9行。

提示：由上两例可以看出，未显式指定子分区的分区，系统会自动创建一个子分区。

--分区模板的应用：

SQL> create table t_partition_rh (id number,name varchar2(50))  2  partition by range(id) subpartition by hash(name)  3  subpartition template (  4   subpartition h1 tablespace tbspart01,  5   subpartition h2 tablespace tbspart02,  6   subpartition h3 tablespace tbspart03,  7   subpartition h4 tablespace tbspart04)(  8  partition t_r_p1 values less than (10) tablespace tbspart01,  9  partition t_r_p2 values less than (20) tablespace tbspart02, 10  partition t_r_p3 values less than (30) tablespace tbspart03, 11  partition t_r_pd values less than (maxvalue) tablespace tbspart04);表已创建。SQL> select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT from user_part_tables where TABLE_NAME = 'T_PARTITION_RH';TABLE_NAME                     PARTITI PARTITION_COUNT------------------------------ ------- ---------------T_PARTITION_RH                 RANGE                 4SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RH';PARTITION_NAME                 HIGH_VALUE           TABLESPACE_NAME------------------------------ -------------------- ------------------------------T_R_P1                         10                   TBSPART01T_R_P2                         20                   TBSPART02T_R_P3                         30                   TBSPART03T_R_PD                         MAXVALUE             TBSPART04SQL> select partition_name,subpartition_name,tablespace_name from user_tab_subpartitions where table_name='T_PARTITION_RH';PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------T_R_P1                         T_R_P1_H4                      TBSPART01T_R_P1                         T_R_P1_H3                      TBSPART01T_R_P1                         T_R_P1_H2                      TBSPART01T_R_P1                         T_R_P1_H1                      TBSPART01T_R_P2                         T_R_P2_H4                      TBSPART02T_R_P2                         T_R_P2_H3                      TBSPART02T_R_P2                         T_R_P2_H2                      TBSPART02T_R_P2                         T_R_P2_H1                      TBSPART02T_R_P3                         T_R_P3_H4                      TBSPART03T_R_P3                         T_R_P3_H3                      TBSPART03T_R_P3                         T_R_P3_H2                      TBSPART03PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------T_R_P3                         T_R_P3_H1                      TBSPART03T_R_PD                         T_R_PD_H4                      TBSPART04T_R_PD                         T_R_PD_H3                      TBSPART04T_R_PD                         T_R_PD_H2                      TBSPART04T_R_PD                         T_R_PD_H1                      TBSPART04已选择16行。