What are number-of-subpartitions of composite range-hash partitioning tables

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、最後根據對比看檢視的結果，發現問題所在。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-752765/，如需轉載，請註明出處，否則將追究法律責任。

What are number-of-subpartitions of composite range-hash partitioning tables

相關文章