关于SUBPARTITION TEMPLATE

来源：互联网发布：淘宝卖的火柴枪合法么编辑：程序博客网时间：2024/06/05 02:34

分区表之 SUBPARTITION TEMPLATE

问题：http://www.itpub.net/581496,2.html

idle>create tablespace tbs1 datafile

2 'd:\ora92\oradata\oracle\tbs1_1.dbf' size 5m;

表空间已创建。

idle>create tablespace tbs2 datafile

2 'd:\ora92\oradata\oracle\tbs2_1.dbf' size 5m;

表空间已创建。

idle>create table AGG_BILL_BY_CITY

2 (

3 HOUR_ID INTEGER not null,

4 CITY_ID INTEGER not null,

5 MONTH_ID INTEGER,

6 CALL_TYPE_ID INTEGER not null,

7 DEAL_ID INTEGER not null,

8 OPERATOR_ID INTEGER not null,

9 FEE_TYPE_ID INTEGER not null,

10 ROAM_TYPE_ID INTEGER not null,

11 CALL_FEE NUMBER,

12 LONG_FEE NUMBER,

13 CALL_NUM INTEGER,

14 BILL_DURATION NUMBER,

15 CALL_DURATION NUMBER,

16 GPRS_IN NUMBER,

17 GPRS_OUT NUMBER,

18 LOAD_TIME DATE default sysdate,

19 SUM_NUM NUMBER

20 )

21 partition by range(HOUR_ID)

22 subpartition by hash(CITY_ID, CALL_TYPE_ID, DEAL_ID, OPERATOR_ID, FEE_TYPE_ID, ROAM_TYPE_ID)

23 subpartitions 2 store in(TBS1,tBS2)

24 (

25 partition P20060527 values less than(2006052800),

26 partition P20060528 values less than(2006052900)

27 );

表已创建。

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

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME

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

AGG_BILL_BY_CITY P20060527 SYS_SUBP7 TBS1

AGG_BILL_BY_CITY P20060527 SYS_SUBP8 TBS2

AGG_BILL_BY_CITY P20060528 SYS_SUBP9 TBS1

AGG_BILL_BY_CITY P20060528 SYS_SUBP10 TBS2

idle> alter table AGG_BILL_BY_CITY add partition P20060530 values less than(2006053000);

表已更改。

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

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME

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

AGG_BILL_BY_CITY P20060527 SYS_SUBP7 TBS1

AGG_BILL_BY_CITY P20060527 SYS_SUBP8 TBS2

AGG_BILL_BY_CITY P20060528 SYS_SUBP9 TBS1

AGG_BILL_BY_CITY P20060528 SYS_SUBP10 TBS2

AGG_BILL_BY_CITY P20060530 SYS_SUBP11 SYSTEM

AGG_BILL_BY_CITY P20060530 SYS_SUBP12 SYSTEM

已选择6行。

---当然，你可以通过以下的方法来改变存储的表空间。

idle> alter table AGG_BILL_BY_CITY move subpartition sys_subp11 tablespace tbs1;

表已更改。

idle> alter table AGG_BILL_BY_CITY move subpartition sys_subp12 tablespace tbs2;

表已更改。

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

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME

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

AGG_BILL_BY_CITY P20060527 SYS_SUBP7 TBS1

AGG_BILL_BY_CITY P20060527 SYS_SUBP8 TBS2

AGG_BILL_BY_CITY P20060528 SYS_SUBP9 TBS1

AGG_BILL_BY_CITY P20060528 SYS_SUBP10 TBS2

AGG_BILL_BY_CITY P20060530 SYS_SUBP11 TBS1

AGG_BILL_BY_CITY P20060530 SYS_SUBP12 TBS2

已选择6行。

----另一种方法，用SUBPARTITION TEMPLATE。

idle>drop table agg_bill_by_city;

表已丢弃。

idle>create table AGG_BILL_BY_CITY

2 (

3 HOUR_ID INTEGER not null,

4 CITY_ID INTEGER not null,

5 MONTH_ID INTEGER,

6 CALL_TYPE_ID INTEGER not null,

7 DEAL_ID INTEGER not null,

8 OPERATOR_ID INTEGER not null,

9 FEE_TYPE_ID INTEGER not null,

10 ROAM_TYPE_ID INTEGER not null,

11 CALL_FEE NUMBER,

12 LONG_FEE NUMBER,

13 CALL_NUM INTEGER,

14 BILL_DURATION NUMBER,

15 CALL_DURATION NUMBER,

16 GPRS_IN NUMBER,

17 GPRS_OUT NUMBER,

18 LOAD_TIME DATE default sysdate,

19 SUM_NUM NUMBER

20 )

21 partition by range(HOUR_ID)

22 subpartition by hash(CITY_ID, CALL_TYPE_ID, DEAL_ID, OPERATOR_ID, FEE_TYPE_ID, ROAM_TYPE_ID)

23 SUBPARTITION TEMPLATE(

24 SUBPARTITION sp1 TABLESPACE tbs1,

25 SUBPARTITION sp2 TABLESPACE tbs2)

26 (

27 partition P20060527 values less than(2006052800),

28 partition P20060528 values less than(2006052900)

29 );

表已创建。

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

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME

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

AGG_BILL_BY_CITY P20060527 P20060527_SP1 TBS1

AGG_BILL_BY_CITY P20060527 P20060527_SP2 TBS2

AGG_BILL_BY_CITY P20060528 P20060528_SP1 TBS1

AGG_BILL_BY_CITY P20060528 P20060528_SP2 TBS2

idle>alter table AGG_BILL_BY_CITY add partition P20060530 values less than(2006053000);

表已更改。

idle>

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

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME

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

AGG_BILL_BY_CITY P20060527 P20060527_SP1 TBS1

AGG_BILL_BY_CITY P20060527 P20060527_SP2 TBS2

AGG_BILL_BY_CITY P20060528 P20060528_SP1 TBS1

AGG_BILL_BY_CITY P20060528 P20060528_SP2 TBS2

AGG_BILL_BY_CITY P20060530 P20060530_SP1 TBS1

AGG_BILL_BY_CITY P20060530 P20060530_SP2 TBS2

已选择6行。

文档上的关于Template

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#357

Modifying a Subpartition Templatehttp://yangtingkun.itpub.net/post/468/20713

http://skygodblue.spaces.msn.com/blog/cns!D18CEE1DB3D21A07!332.entry

Btw:

我上面做的例子(332)当中，最后一个 add partition前忘了最重要的一个步骤：

idle>alter table test_part MODIFY DEFAULT ATTRIBUTES tablespace tbs4;

表已更改。

修改 test_part的 default attributes 。

来源：http://zhaoyan0922-photo.blog.163.com/blog/static/26238441200812854350277/