What are number-of-subpartitions of composite range-hash partitioning tables (number of subpartitions 在组合分区表中的作用 ) ？

10g 官档中： http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm#sthref2602

CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)

  PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)

    SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)

      (PARTITION p1 VALUES LESS THAN (1000),

       PARTITION p2 VALUES LESS THAN (2000),

       PARTITION p3 VALUES LESS THAN (MAXVALUE));

答案：

Number of subpartitions ( subpartitions 8 store in ) ：每个分区 （ P1 、 P2 、 P3 ） 按照 8 个子分区 （ oracle 自动分赔的子分区） 存储在 store in 指定的表空间当中

1 、 Create table t1 and Subpartition=1

doudou@TEST> CREATE TABLE t1 (equipno NUMBER, equipname VARCHAR(32), price NUMBER)

  2     PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)

  3       SUBPARTITIONS 1 STORE IN (users, doudou, maclean)

  4         (PARTITION p1 VALUES LESS THAN (1000),

  5          PARTITION p2 VALUES LESS THAN (2000),

  6          PARTITION p3 VALUES LESS THAN (MAXVALUE));

Table created.

doudou@TEST> select table_name,partition_name,subpartition_name,subpartition_position,tablespace_name from sys.user_tab_subpartitions t where table_name='T1'

  2   order by table_name,subpartition_name,tablespace_name

TABLE_NAME            PARTITION_NAME        SUBPARTITION_NAME     SUBPARTITION_POSITION TABLESPACE_NAME

-------------------- -------------------- -------------------- --------------------- --------------------

T1                    P1                    SYS_SUBP62                                1 USERS

T1                    P2                    SYS_SUBP63                                1 USERS

T1                    P3                    SYS_SUBP64                                1 USERS

【当 subpartition=1 时，一个分区只有一个自分区，每个子分区存储在一个单独表空间】

2 、 Create table t2 and Subpartition=2

doudou@TEST> CREATE TABLE t2 (equipno NUMBER, equipname VARCHAR(32), price NUMBER)

  2     PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)

  3       SUBPARTITIONS 2 STORE IN (users, doudou, maclean)

  4         (PARTITION p1 VALUES LESS THAN (1000),

  5          PARTITION p2 VALUES LESS THAN (2000),

  6          PARTITION p3 VALUES LESS THAN (MAXVALUE));

Table created.

doudou@TEST> select table_name,partition_name,subpartition_name,subpartition_position,tablespace_name from sys.user_tab_subpartitions t where table_name='T2'

  2   order by table_name,subpartition_name,tablespace_name;

TABLE_NAME            PARTITION_NAME        SUBPARTITION_NAME     SUBPARTITION_POSITION TABLESPACE_NAME

-------------------- -------------------- -------------------- --------------------- --------------------

T2                    P1                    SYS_SUBP65                                1 USERS

T2                    P1                    SYS_SUBP66                                2 DOUDOU

T2                    P2                    SYS_SUBP67                                1 USERS

T2                    P2                    SYS_SUBP68                                2 DOUDOU

T2                    P3                    SYS_SUBP69                                1 USERS

T2                    P3                    SYS_SUBP70                                2 DOUDOU

【当 subpartition=2 时，每个分区有 2 个子分区，每个子分区存储在一个单独表空间】

3 、 create table t3 and subpartition=3

doudou@TEST> CREATE TABLE t3 (equipno NUMBER, equipname VARCHAR(32), price NUMBER)

  2     PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)

  3       SUBPARTITIONS 3 STORE IN (users, doudou, maclean)

  4         (PARTITION p1 VALUES LESS THAN (1000),

  5          PARTITION p2 VALUES LESS THAN (2000),

  6          PARTITION p3 VALUES LESS THAN (MAXVALUE));

Table created.

doudou@TEST>

doudou@TEST> select table_name,partition_name,subpartition_name,subpartition_position,tablespace_name from sys.user_tab_subpartitions t where table_name='T3'

  2   order by table_name,subpartition_name,tablespace_name;

TABLE_NAME            PARTITION_NAME        SUBPARTITION_NAME     SUBPARTITION_POSITION TABLESPACE_NAME

-------------------- -------------------- -------------------- --------------------- --------------------

T3                    P1                    SYS_SUBP71                                1 USERS

T3                    P1                    SYS_SUBP72                                2 DOUDOU

T3                    P1                    SYS_SUBP73                                3 MACLEAN

T3                    P2                    SYS_SUBP74                                1 USERS

T3                    P2                    SYS_SUBP75                                2 DOUDOU

T3                    P2                    SYS_SUBP76                                3 MACLEAN

T3                    P3                    SYS_SUBP77                                1 USERS

T3                    P3                    SYS_SUBP78                                2 DOUDOU

T3                    P3                    SYS_SUBP79                                3 MACLEAN

【当 subpartition=3 时，每个分区有 3 个子分区，每个子分区存储在一个单独表空间】

4 、 create table t4 and subpartition=4

doudou@TEST> CREATE TABLE t4 (equipno NUMBER, equipname VARCHAR(32), price NUMBER)

  2     PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)

  3       SUBPARTITIONS 4 STORE IN (users, doudou, maclean)

  4         (PARTITION p1 VALUES LESS THAN (1000),

  5          PARTITION p2 VALUES LESS THAN (2000),

  6          PARTITION p3 VALUES LESS THAN (MAXVALUE));

Table created.

doudou@TEST> select table_name,partition_name,subpartition_name,subpartition_position,tablespace_name from sys.user_tab_subpartitions t where table_name='T4'

  2   order by table_name,subpartition_name,tablespace_name;

TABLE_NAME            PARTITION_NAME        SUBPARTITION_NAME     SUBPARTITION_POSITION TABLESPACE_NAME

-------------------- -------------------- -------------------- --------------------- --------------------

T4                    P1                    SYS_SUBP80                                1 USERS

T4                    P1                    SYS_SUBP81                                2 DOUDOU

T4                    P1                    SYS_SUBP82                                3 MACLEAN

T4                    P1                    SYS_SUBP83                                4 USERS

T4                    P2                    SYS_SUBP84                                1 USERS

T4                    P2                    SYS_SUBP85                                2 DOUDOU

T4                    P2                    SYS_SUBP86                                3 MACLEAN

T4                    P2                    SYS_SUBP87                                4 USERS

T4                    P3                    SYS_SUBP88                                1 USERS

T4                    P3                    SYS_SUBP89                                2 DOUDOU

T4                    P3                    SYS_SUBP90                                3 MACLEAN

T4                    P3                    SYS_SUBP91                                4 USERS

【当 subpartition=4 时，每个分区有 4 个子分区，每个子分区存储在一个单独表空间】

5 、 create table t5 and subpartition=8

doudou@TEST> CREATE TABLE t5 (equipno NUMBER, equipname VARCHAR(32), price NUMBER)

  2     PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)

  3       SUBPARTITIONS 8 STORE IN (users, doudou, maclean)

  4         (PARTITION p1 VALUES LESS THAN (1000),

  5          PARTITION p2 VALUES LESS THAN (2000),

  6          PARTITION p3 VALUES LESS THAN (MAXVALUE));

Table created.

doudou@TEST> select table_name,partition_name,subpartition_name,subpartition_position,tablespace_name from sys.user_tab_subpartitions t where table_name='T5'

  2   order by table_name,subpartition_name,tablespace_name;

TABLE_NAME            PARTITION_NAME        SUBPARTITION_NAME     SUBPARTITION_POSITION TABLESPACE_NAME

-------------------- -------------------- -------------------- --------------------- --------------------

T5                    P2                    SYS_SUBP100                               1 USERS

T5                    P2                    SYS_SUBP101                               2 DOUDOU

T5                    P2                    SYS_SUBP102                               3 MACLEAN

T5                    P2                    SYS_SUBP103                               4 USERS

T5                    P2                    SYS_SUBP104                               5 DOUDOU

T5                    P2                    SYS_SUBP105                               6 MACLEAN

T5                    P2                    SYS_SUBP106                               7 USERS

T5                    P2                    SYS_SUBP107                               8 DOUDOU

T5                    P3                    SYS_SUBP108                               1 USERS

T5                    P3                    SYS_SUBP109                               2 DOUDOU

T5                    P3                    SYS_SUBP110                               3 MACLEAN

T5                    P3                    SYS_SUBP111                               4 USERS

T5                    P3                    SYS_SUBP112                               5 DOUDOU

T5                    P3                    SYS_SUBP113                               6 MACLEAN

T5                    P3                    SYS_SUBP114                               7 USERS

T5                    P3                    SYS_SUBP115                               8 DOUDOU

T5                    P1                    SYS_SUBP92                                1 USERS

T5                    P1                    SYS_SUBP93                                2 DOUDOU

T5                    P1                    SYS_SUBP94                                3 MACLEAN

T5                    P1                    SYS_SUBP95                                4 USERS

T5                    P1                    SYS_SUBP96                                5 DOUDOU

T5                    P1                    SYS_SUBP97                                6 MACLEAN

T5                    P1                    SYS_SUBP98                                7 USERS

T5                    P1                    SYS_SUBP99                                8 DOUDOU

【当 subpartition=8 时，每个分区有 8 个子分区，每个子分区存储在一个单独表空间】

总结： number of subpartitions

1、 subpartiton=N 时，每个分区有 N 个子分区 【 N 的范围 (1-1048575) 】

2、 每个子分区存储在一个单独的表空间

附表

Subpartition number 范围知识点

http://docs.oracle.com/cd/B19306_01/server.102/b14219/e12700.htm#sthref4406

本文研究： When to Use Composite Range-Hash Partitioning

http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm#sthref2602

思路：

1、   subpartition 8 不是很懂，通过 GOOGLE ， MOS ，百度。找到 user_ind_partitions 视图，然后就想起来了， user_tab_partitions 视图。 PLSQL 中 select * from user_tab_partitions 发现了一些不同，继而发现问题。

2、   最后根据对比看视图的结果，发现问题所在。