创建range-hash(范围-哈希)组合分区!

--创建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
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值