subpartition template学习

在创建分区表时，使用subpartition template，可以避免增加分区时，子分区建在默认表空间。

一。没有使用subpartition template

1.创建一个没有subpartition template的分区表

create table subpar_notemplate (deptno number, empname varchar(32), grade number)  

partition by range(deptno) subpartition by hash(empname)

subpartitions 3 store in(data01,data02)

  4  (partition p1 values less than (1000),

  5   partition p2 values less than (2000)

  6  );

 

Table created.

2.查看分区表各分区存储情况：

SQL> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;

 

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME

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

 

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP63                     DATA01

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP62                     DATA02

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP61                     DATA01

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP66                     DATA01

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP65                     DATA02

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP64                     DATA01

 

4.增加一个分区：

SQL> alter table SUBPAR_NOTEMPLATE add partition p3 values less than(3000);

 

Table altered.

5.可以看到新增加的子分区在用户默认表空间中。

SQL> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;

 

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME

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

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP63                     DATA01

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP62                     DATA02

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP61                     DATA01

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP66                     DATA01

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP65                     DATA02

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP64                     DATA01

SUBPAR_NOTEMPLATE             P3                             SYS_SUBP69                     USERS

SUBPAR_NOTEMPLATE             P3                             SYS_SUBP68                     USERS

SUBPAR_NOTEMPLATE             P3                             SYS_SUBP67                     USERS

6.可以用下面的方法手工移动

SQL> alter table SUBPAR_NOTEMPLATE move subpartition SYS_SUBP67 data03;

alter table SUBPAR_NOTEMPLATE move subpartition SYS_SUBP67 data03

                                                           *

ERROR at line 1:

ORA-14160: this physical attribute may not be specified for a table subpartition

 

SQL> alter table subpar_notemplate move subpartition SYS_SUBP67 tablespace DATA01;

 

Table altered.

 

SQL> alter table subpar_notemplate move subpartition SYS_SUBP68 tablespace DATA02;

 

Table altered.

 

SQL> alter table subpar_notemplate move subpartition SYS_SUBP69 tablespace DATA03;

 

Table altered.

7.查看移动后的结果：

SQL> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;

 

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME

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

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP63                     DATA01

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP62                     DATA02

SUBPAR_NOTEMPLATE              P1                             SYS_SUBP61                     DATA01

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP66                     DATA01

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP65                     DATA02

SUBPAR_NOTEMPLATE              P2                             SYS_SUBP64                     DATA01

SUBPAR_NOTEMPLATE             P3                             SYS_SUBP69                     DATA03

SUBPAR_NOTEMPLATE             P3                             SYS_SUBP68                     DATA02

SUBPAR_NOTEMPLATE             P3                             SYS_SUBP67                     DATA01

 

二。使用subpartition template

 

8.可以有另一种更方便的方法实现。在创建分区表时，使用suppartition template，可以避免增加分区时建在默认表空间。

 

SQL> create table subpar_template (deptno number, empname varchar(32), grade number)  

partition by range(deptno) subpartition by hash(empname)

  3  subpartition template

  4  (subpartition st1 tablespace data01,

  5   subpartition st2 tablespace data02,

  6   subpartition st3 tablespace data03

  7  )

  8  (partition p1 values less than (1000),

  9   partition p2 values less than (2000)

 10  );

 

Table created.

9.查看分区表的存储情况

SQL> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;

 

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME

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

SUBPAR_TEMPLATE                P1                             P1_ST3                         DATA03

SUBPAR_TEMPLATE                P1                             P1_ST2                         DATA02

SUBPAR_TEMPLATE                P1                             P1_ST1                         DATA01

SUBPAR_TEMPLATE                P2                             P2_ST3                         DATA03

SUBPAR_TEMPLATE                P2                             P2_ST2                         DATA02

SUBPAR_TEMPLATE                P2                             P2_ST1                         DATA01

 

 

10.增加一个分区

 

SQL> alter table subpar_template add partition p3 values less than(3000);

 

Table altered.

11.查看分区表各个子分区的存储情况。

SQL> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;

 

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              TABLESPACE_NAME

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

SUBPAR_TEMPLATE                P1                             P1_ST3                         DATA03

SUBPAR_TEMPLATE                P1                             P1_ST2                         DATA02

SUBPAR_TEMPLATE                P1                             P1_ST1                         DATA01

SUBPAR_TEMPLATE                P2                             P2_ST3                         DATA03

SUBPAR_TEMPLATE                P2                             P2_ST2                         DATA02

SUBPAR_TEMPLATE                P2                             P2_ST1                         DATA01

SUBPAR_TEMPLATE                P3                             P3_ST3                         DATA03

SUBPAR_TEMPLATE                P3                             P3_ST2                         DATA02

SUBPAR_TEMPLATE                P3                             P3_ST1                         DATA01