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

最新推荐文章于 2022-10-24 22:40:44 发布

事后诸葛亮

最新推荐文章于 2022-10-24 22:40:44 发布

阅读量3.5k

点赞数

分类专栏： partition 文章标签： table less sql user

本文链接：https://blog.csdn.net/zq9017197/article/details/6831177

版权

partition 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

--创建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                 4

SQL> 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                   TBSPART01
T_R_P2                         20                   TBSPART02
T_R_P3                         30                   TBSPART03
T_R_PD                         MAXVALUE             TBSPART04

SQL> 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                     TBSPART01
T_R_P1                         SYS_SUBP35                     TBSPART01
T_R_P1                         SYS_SUBP34                     TBSPART01
T_R_P1                         SYS_SUBP33                     TBSPART01
T_R_P2                         SYS_SUBP40                     TBSPART02
T_R_P2                         SYS_SUBP39                     TBSPART02
T_R_P2                         SYS_SUBP38                     TBSPART02
T_R_P2                         SYS_SUBP37                     TBSPART02
T_R_P3                         SYS_SUBP44                     TBSPART03
T_R_P3                         SYS_SUBP43                     TBSPART03
T_R_P3                         SYS_SUBP42                     TBSPART03

PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_R_P3                         SYS_SUBP41                     TBSPART03
T_R_PD                         SYS_SUBP48                     TBSPART04
T_R_PD                         SYS_SUBP47                     TBSPART04
T_R_PD                         SYS_SUBP46                     TBSPART04
T_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                 4

SQL> 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                   TBSPART01
T_R_P2                         20                   TBSPART02
T_R_P3                         30                   TBSPART03
T_R_PD                         MAXVALUE             TBSPART04

SQL> 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                     TBSPART01
T_R_P2                         SYS_SUBP50                     TBSPART02
T_R_P3                         T_R_P3_H3                      TBSPART03
T_R_P3                         T_R_P3_H2                      TBSPART02
T_R_P3                         T_R_P3_H1                      TBSPART01
T_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                 4

SQL> 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                   TBSPART01
T_R_P2                         20                   TBSPART02
T_R_P3                         30                   TBSPART03
T_R_PD                         MAXVALUE             TBSPART04

SQL> 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                     TBSPART01
T_R_P2                         T_R_P2_H2                      TBSPART02
T_R_P2                         T_R_P2_H1                      TBSPART01
T_R_P3                         SYS_SUBP55                     TBSPART03
T_R_P3                         SYS_SUBP54                     TBSPART02
T_R_P3                         SYS_SUBP53                     TBSPART01
T_R_PD                         T_R_P3_H3                      TBSPART03
T_R_PD                         T_R_P3_H2                      TBSPART02
T_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                 4

SQL> 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                   TBSPART01
T_R_P2                         20                   TBSPART02
T_R_P3                         30                   TBSPART03
T_R_PD                         MAXVALUE             TBSPART04

SQL> 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                      TBSPART01
T_R_P1                         T_R_P1_H3                      TBSPART01
T_R_P1                         T_R_P1_H2                      TBSPART01
T_R_P1                         T_R_P1_H1                      TBSPART01
T_R_P2                         T_R_P2_H4                      TBSPART02
T_R_P2                         T_R_P2_H3                      TBSPART02
T_R_P2                         T_R_P2_H2                      TBSPART02
T_R_P2                         T_R_P2_H1                      TBSPART02
T_R_P3                         T_R_P3_H4                      TBSPART03
T_R_P3                         T_R_P3_H3                      TBSPART03
T_R_P3                         T_R_P3_H2                      TBSPART03

PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_R_P3                         T_R_P3_H1                      TBSPART03
T_R_PD                         T_R_PD_H4                      TBSPART04
T_R_PD                         T_R_PD_H3                      TBSPART04
T_R_PD                         T_R_PD_H2                      TBSPART04
T_R_PD                         T_R_PD_H1                      TBSPART04

已选择16行。

事后诸葛亮

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
创建range-hash（范围-哈希）组合分区！

--创建range-hash组合分区：SQL> create table t_partition_rh (id number,name varchar2(50)) 2 partition by range(id) subpartition by hash(name)
复制链接

扫一扫