--创建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行。