深入学习分区表及分区索引（详解oracle分区）

已选择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 1

JSSWEB> 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 10 T_R_P2 2 20 T_R_P3 3 30 T_R_PD 3 MAXVALUE

JSSWEB> 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_SUBP152 TBSPART01 T_R_P2 T_R_P2_H2 TBSPART02 T_R_P2 T_R_P2_H1 TBSPART01 T_R_P3 SYS_SUBP155 TBSPART03 T_R_P3 SYS_SUBP154 TBSPART02 T_R_P3 SYS_SUBP153 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行。

提示：由上两例可以看出，未显式指定子分区的分区，系统会自动创建一个子分区。

① 分区模板的应用

oracle还提供了一种称为分区模板的功能，在指定子分区信赖列之后，制订子分区的存储模板，各个分区即会按照子分区模式创建子分区，例如： JSSWEB> 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);

表已创建。

JSSWEB> 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 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 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行。

=======================================