深入学习分区表及分区索引(5)--创建range-hash组合分区

来源：互联网发布：大数据应用领域编辑：程序博客网时间：2024/06/08 03:25

创建range-hash组合分区

语法如下：图[composite_partitioning.gif]

需要我们指定的有：

l column_list:分区依赖列(支持多个，中间以逗号分隔);

l subpartition:子分区方式，有两处：

n Subpartition_by_list:语法与list分区完全相同，只不过把关键字partition换成subpartition

n Subpartition_by_hash:语法与hash分区完全相同，只不过把关键字partition换成subpartition

l partition:分区名称;

l range_partition_values_clause:与range分区范围值的语法;

l tablespace_clause:分区的存储属性，例如所在表空间等属性(可为空)，默认继承基表所在表空间的属性。

组合分区相对于普通分区，语法上稍稍复杂了一些，但也正因如此，其子分区的创建可以非常灵活，下面分别举几个例子(注：仅示例，并非穷举所有形式)

①　为所有分区各创建4个hash子分区

JSSWEB> 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);表已创建。JSSWEB> select partitioning_type,subpartitioning_type,partition_count,def_subpartition_count  2  From user_part_tables where table_name='T_PARTITION_RH';PARTITI SUBPART PARTITION_COUNT DEF_SUBPARTITION_COUNT------- ------- --------------- ----------------------RANGE   HASH                  4                      4JSSWEB> select partition_name,subpartition_count,high_value  2  from user_tab_partitions where table_name='T_PARTITION_RH';PARTITION_NAME  SUBPARTITION_COUNT HIGH_VALUE--------------- ------------------ ----------T_R_P2                           4 20T_R_P3                           4 30T_R_PD                           4 MAXVALUET_R_P1                           4 10JSSWEB> select partition_name,subpartition_name,tablespace_name  2  from user_tab_subpartitions where table_name='T_PARTITION_RH';PARTITION_NAME  SUBPARTITION_NAME              TABLESPACE_NAME--------------- ------------------------------ --------------------T_R_P2          SYS_SUBP140                    TBSPART02T_R_P2          SYS_SUBP139                    TBSPART02T_R_P2          SYS_SUBP138                    TBSPART02T_R_P2          SYS_SUBP137                    TBSPART02T_R_P3          SYS_SUBP144                    TBSPART03T_R_P3          SYS_SUBP143                    TBSPART03T_R_P3          SYS_SUBP142                    TBSPART03T_R_P3          SYS_SUBP141                    TBSPART03T_R_PD          SYS_SUBP148                    TBSPART04T_R_PD          SYS_SUBP147                    TBSPART04T_R_PD          SYS_SUBP146                    TBSPART04T_R_PD          SYS_SUBP145                    TBSPART04T_R_P1          SYS_SUBP133                    TBSPART01T_R_P1          SYS_SUBP136                    TBSPART01T_R_P1          SYS_SUBP135                    TBSPART01T_R_P1          SYS_SUBP134                    TBSPART01已选择16行。这里我们要学到一个新的数据字典：user_tab_subpartitions，用于查询表的子分区信息。②　对某个分区创建hash子分区JSSWEB> 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);表已创建。JSSWEB> select partitioning_type,subpartitioning_type,partition_count,def_subpartition_count  2  From user_part_tables where table_name='T_PARTITION_RH';PARTITI SUBPART PARTITION_COUNT DEF_SUBPARTITION_COUNT------- ------- --------------- ----------------------RANGE   HASH                  4                      1JSSWEB> select partition_name,subpartition_count,high_value  2  from user_tab_partitions where table_name='T_PARTITION_RH';PARTITION_NAME  SUBPARTITION_COUNT HIGH_VALUE--------------- ------------------ ----------T_R_P1                           1 10T_R_P2                           1 20T_R_P3                           3 30T_R_PD                           1 MAXVALUEJSSWEB> select partition_name,subpartition_name,tablespace_name  2  from user_tab_subpartitions where table_name='T_PARTITION_RH';PARTITION_NAME  SUBPARTITION_NAME              TABLESPACE_NAME--------------- ------------------------------ --------------------T_R_P1          SYS_SUBP149                    TBSPART01T_R_P2          SYS_SUBP150                    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_SUBP151                    TBSPART04已选择6行。当然，还可以给各个分区指定不同的子分区JSSWEB> 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) 13  );表已创建。JSSWEB> select partitioning_type,subpartitioning_type,partition_count,def_subpartition_count  2  From user_part_tables where table_name='T_PARTITION_RH';PARTITI SUBPART PARTITION_COUNT DEF_SUBPARTITION_COUNT------- ------- --------------- ----------------------RANGE   HASH                  4                      1JSSWEB> select partition_name,subpartition_count,high_value  2  from user_tab_partitions where table_name='T_PARTITION_RH';PARTITION_NAME  SUBPARTITION_COUNT HIGH_VALUE