创建range-list（范围-列表）组合分区！

--创建range-list组合分区：

SQL> create table t_partition_rl (id number,name varchar2(50))
  2  partition by range(id) subpartition by list(name)
  3  subpartition template (
  4   subpartition l1 values ('aa') tablespace tbspart01,
  5   subpartition l2 values ('bb') tablespace tbspart02,
  6   subpartition l3 values ('cc') tablespace tbspart03,
  7   subpartition l4 values ('dd') 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_RL';

TABLE_NAME                     PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
T_PARTITION_RL                 RANGE                 4

SQL> select PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions where TABLE_NAME = 'T_PARTITION_RL';

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_RL';

PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_R_P1                         T_R_P1_L4                      TBSPART01
T_R_P1                         T_R_P1_L3                      TBSPART01
T_R_P1                         T_R_P1_L2                      TBSPART01
T_R_P1                         T_R_P1_L1                      TBSPART01
T_R_P2                         T_R_P2_L4                      TBSPART02
T_R_P2                         T_R_P2_L3                      TBSPART02
T_R_P2                         T_R_P2_L2                      TBSPART02
T_R_P2                         T_R_P2_L1                      TBSPART02
T_R_P3                         T_R_P3_L4                      TBSPART03
T_R_P3                         T_R_P3_L3                      TBSPART03
T_R_P3                         T_R_P3_L2                      TBSPART03

PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_R_P3                         T_R_P3_L1                      TBSPART03
T_R_PD                         T_R_PD_L4                      TBSPART04
T_R_PD                         T_R_PD_L3                      TBSPART04
T_R_PD                         T_R_PD_L2                      TBSPART04
T_R_PD                         T_R_PD_L1                      TBSPART04

已选择16行。

--对于复合分区的local索引：

SQL> create index idx_part_rl_id on t_partition_rl(id) local;

索引已创建。

SQL> select table_name,partitioning_type,partition_count,def_subpartition_count from user_part_indexes where index_name = 'IDX_PART_RL_ID';

TABLE_NAME                     PARTITI PARTITION_COUNT DEF_SUBPARTITION_COUNT
------------------------------ ------- --------------- ----------------------
T_PARTITION_RL                 RANGE                 4                      4

SQL> select partition_name,subpartition_count,high_value from user_ind_partitions where index_name = 'IDX_PART_RL_ID';

PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE
------------------------------ ------------------ --------------------
T_R_P1                                          4 10
T_R_P2                                          4 20
T_R_P3                                          4 30
T_R_PD                                          4 MAXVALUE

SQL> select partition_name,subpartition_name,high_value,tablespace_name from user_ind_subpartitions where index_name = 'IDX_PART_RL_ID';

PARTITION_NAME                 SUBPARTITION_NAME              HIGH_VALUE           TABLESPACE_NAME
------------------------------ ------------------------------ -------------------- ------------------------------
T_R_P1                         T_R_P1_L1                      'aa'                 TBSPART01
T_R_P1                         T_R_P1_L2                      'bb'                 TBSPART01
T_R_P1                         T_R_P1_L3                      'cc'                 TBSPART01
T_R_P1                         T_R_P1_L4                      'dd'                 TBSPART01
T_R_P2                         T_R_P2_L1                      'aa'                 TBSPART02
T_R_P2                         T_R_P2_L2                      'bb'                 TBSPART02
T_R_P2                         T_R_P2_L3                      'cc'                 TBSPART02
T_R_P2                         T_R_P2_L4                      'dd'                 TBSPART02
T_R_P3                         T_R_P3_L1                      'aa'                 TBSPART03
T_R_P3                         T_R_P3_L2                      'bb'                 TBSPART03
T_R_P3                         T_R_P3_L3                      'cc'                 TBSPART03

PARTITION_NAME                 SUBPARTITION_NAME              HIGH_VALUE           TABLESPACE_NAME
------------------------------ ------------------------------ -------------------- ------------------------------
T_R_P3                         T_R_P3_L4                      'dd'                 TBSPART03
T_R_PD                         T_R_PD_L1                      'aa'                 TBSPART04
T_R_PD                         T_R_PD_L2                      'bb'                 TBSPART04
T_R_PD                         T_R_PD_L3                      'cc'                 TBSPART04
T_R_PD                         T_R_PD_L4                      'dd'                 TBSPART04

已选择16行。