Create a Range-hash (range-hash) combination partition!

--Create a range-hash combination partition:

Sql> CREATE TABLE T_partition_rh (ID number,name varchar2) 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 (a) tabl  Espace tbspart01, 5 partition T_R_P2 values less than (m) tablespace tbspart02, 6 partition T_R_P3 values less than

(a) Tablespace tbspart03, 7 partition T_R_PD values less than (MaxValue) tablespace);

Table has been created.

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_partiti

ONS WHERE table_name = ' T_PARTITION_RH '; Partition_name high_value tablespace_name-------------------------------------------------- ------------------------------T_R_P1 TBSPART01 T_R_P2                         TBSPART02 t_r_p3 TBSPART03 T_R_PD MAXVALUE TBSPART04 sql> Select Partition_name,subpartition_name,tablespace_name from User_tab_subpart

itions 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_sub                         P37 TBSPART02 t_r_p3 sys_subp44 TBSPART03 t_r_p3 Sys_subp43 TBSPART03 t_r_p3 sys_subp42 tbsp ART03 partition_name subpartition_name tablespace_name------------------------------------ ------------------------------------------------------t_r_p3 sys_subp41 tbsp                     ART03 t_r_pd sys_subp48 TBSPART04 t_r_pd sys_subp47                         TBSPART04 t_r_pd sys_subp46 TBSPART04 t_r_pd Sys_subp45 TBSPART04 has selected 16 rows.

--Create a hash sub partition for a partition

Sql> CREATE TABLE T_partition_rh (ID number,name varchar2 (m)) 2 partition by range (ID) subpartition by hash (name) ( 3 partition T_R_P1 values less than (a) tablespace tbspart01, 4 partition T_R_P2 values less than (m) tablespace t bspart02, 5 partition T_R_P3 values less than (a) Tablespace tbspart03 6 (subpartition t_r_p3_h1 tablespace Tbspart 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);

Table has been created.

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_partiti

ONS WHERE table_name = ' T_PARTITION_RH '; Partition_name High_valuE tablespace_name--------------------------------------------------------------------------------t_r_p1                         TBSPART01 T_R_P2 TBSPART02 T_R_P3 TBSPART03 t_r_pd MAXVALUE TBSPART04 sql&gt ; 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 has selected 6 rows. 

--You can also specify different child partitions for each partition:

Sql> CREATE TABLE T_partition_rh (ID number,name varchar2 (m)) 2 partition by range (ID) subpartition by hash (name) ( 3 partition T_R_P1 values less than (a) tablespace tbspart01, 4 partition T_R_P2 values less than (m) tablespace t bspart02 5 (subpartition t_r_p2_h1 tablespace tbspart01, 6 subpartition t_r_p2_h2 tablespace tbspart02), 7 parti   tion t_r_p3 values less than tablespace tbspart03 8 subpartitions 3 store in (tbspart01,tbspart02,tbspart03), 9    Partition T_R_PD values less than (MaxValue) tablespace tbspart04 (subpartition t_r_p3_h1 tablespace tbspart01, 11

Subpartition t_r_p3_h2 tablespace tbspart02, subpartition t_r_p3_h3 tablespace));

Table has been created.

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 TBSPART01 T_R_P2                         TBSPART02 t_r_p3 TBSPART03 T_R_PD MAXVALUE TBSPART04 sql> Select Partition_name,subpartition_name,tablespace_name from User_tab_subpartiti

ONS 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 has selected 9 rows.

Tip: As you can see from the last two examples, you do not explicitly specify a partition for a child partition, and a child partition is created automatically by the system.

--Application of partition Template:

Sql> CREATE TABLE T_partition_rh (ID number,name varchar2) 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 (a) Tablespace tbspart01, 9 partition T_R_P2 values less than (a) tablespace tbspart02, partition T_R_P3 values

Less than () tablespace tbspart03, partition T_R_PD values less than (MaxValue) tablespace tbspart04);

Table has been created.

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_partiti ONS WHERE table_name = ' T_partItion_rh '; Partition_name high_value tablespace_name----------------------------------------------------                         ----------------------------T_R_P1 TBSPART01 T_R_P2                         TBSPART02 t_r_p3 TBSPART03 T_R_PD MAXVALUE TBSPART04 sql> Select Partition_name,subpartition_name,tablespace_name from User_tab_subpartiti

ONS 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 has selected 16 rows.