Set_subpartition_template 和注意事项

set_subpartition_template

Use the set_subpartition_template clause tocreate or replace existing default range, list, or hash subpartitiondefinitions for each table partition. This clause is valid only forcomposite-partitioned tables. It replaces the existing subpartition template orcreates a new template if you have not previously created one. Existingsubpartitions are not affected, nor are existing local and global indexes.However, subsequent partitioning operations (such as add and merge operations)will use the new template.

You can drop anexisting subpartition template by specifying ALTERTABLEtableSETSUBPARTITIONTEMPLATE().

Note:

When you specify tablespace storage forthe subpartition template, it does not override any tablespace storage you havespecified explicitly for the partitions of table. To specifytablespace storage for subpartitions, do one of these things:

·Omit tablespace storage at the partition level andspecify tablespace storage in the subpartition template.

·Define individual subpartitions with specific tablespacestorage.

Restrictions on SubpartitionTemplates Refer to "Restrictions on Subpartition Templates" inthe documentation on CREATETABLE.

Set SubpartitionTemplates 其主要目的是方便与子分区的扩充。这里要比modify_table_subpartition方便多，modify_table_subpartition主要是This clause applies onlyto composite-partitioned tables. Its subclauses let you modify thecharacteristics of an individual range, list, or hash subpartition.Theshrink_clause lets you compact an individual subpartition segment。只能添加一个分区。并且致使在一个partition中。如果要在另外一个分区需要添加子分区时，还得继续手动添加子分区。

而set_subpartition_templates 是分区表中的子分区模板，在建立分区的时，子分区会自动根据subpartition_templates 创建子分区。


191135714.gif


例如：

Range+list组合分区


CREATE TABLE "USER1"."TABLE_NAME"

( "CS" NUMBER(30,0),

"CM_ID" NUMBER(7,0),

"CC" VARCHAR2(16),

"CSD" VARCHAR2(16),

"CMP" DATE,

"COPRT" VARCHAR2(9),

"CBEHAVIOR" VARCHAR2(50),

"SDDS" NUMBER(10,0),

"CYUYD" NUMBER(10,0),

"DRTIME" DATE,

"CE" DATE NOT NULL ENABLE,

"CBROADE" DATE,

"CFIME" VARCHAR2(128),

"CCE" VARCHAR2(128),

"CTYPE" NUMBER(10,0),

"CMEMO" VARCHAR2(128),

"CVERSION" NUMBER(10,0) DEFAULT 1,

"CVD" NUMBER(10,0),

"CGROUIUY" NUMBER(3,0),

"CCSDE" DATE DEFAULT sysdate,

"DSFD" NUMBER(10,0) DEFAULT 0

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS255 NOLOGGING

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)

TABLESPACE "TS_2012_6"

PARTITION BY RANGE ("DRTIME")

SUBPARTITION BY LIST("CYUYD")

SUBPARTITION TEMPLATE (

SUBPARTITION "GROUP1" VALUES ( 1)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP2" VALUES ( 2)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP3" VALUES ( 3)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP4" VALUES ( 4)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP5" VALUES ( 5)

TABLESPACE "TS_2012_6" )

(PARTITION"P_TEST_20130201" VALUESLESS THAN (TO_DATE(' 2013-02-02 02:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 1048576 NEXT 1048576MINEXTENTS 1 MAXEXTENTS 2147483645

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)

TABLESPACE "TS_2012_6" ,

PARTITION"P_TEST_20130202" VALUESLESS THAN (TO_DATE(' 2013-02-03 02:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 1048576 NEXT 1048576MINEXTENTS 1 MAXEXTENTS 2147483645

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)

TABLESPACE "TS_2012_6" ,

PARTITION"P_TEST_20130203" VALUESLESS THAN (TO_DATE(' 2013-02-04 02:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 1048576 NEXT 1048576MINEXTENTS 1 MAXEXTENTS 2147483645

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)

TABLESPACE "TS_2012_6" ,

PARTITION"P_TEST_20130204" VALUESLESS THAN (TO_DATE(' 2013-02-05 02:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 1048576 NEXT 1048576MINEXTENTS 1 MAXEXTENTS 2147483645

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)

TABLESPACE "TS_2012_6" ,

PARTITION "SOURCE_P_DEFAULT" VALUES LESS THAN (MAXVALUE)

PCTFREE 10PCTUSED 40 INITRANS 1 MAXTRANS 255

STORAGE(INITIAL 1048576 NEXT 1048576MINEXTENTS 1 MAXEXTENTS 2147483645

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)

TABLESPACE "TS_2012_6" )

另外有重要问题：

遇到子分区不够用的时候，需要手动修改subpartition templates 如：

ALTER TABLEVAV_ACT_SOURCE SET

SUBPARTITION TEMPLATE (

SUBPARTITION "GROUP1" VALUES ( 1)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP2" VALUES ( 2)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP3" VALUES ( 3)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP4" VALUES ( 4)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP5" VALUES ( 5)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP6" VALUES ( 6)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP7" VALUES ( 7)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP8" VALUES ( 8)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP9" VALUES ( 9)

TABLESPACE "TS_2012_6" ,

SUBPARTITION "GROUP10" VALUES (10 )

TABLESPACE "TS_2012_6" )

值得注意的是：

大家在创建完subpartition template 后，再split partition default分区时，就会发现新建的分区没有按照新的subpartition template 来创建子分区，却是按照原来的subpartitiontemplate来创建的。这是因为default partition创建时是按照原来的subpartition template来创建的。因此需要dropdefault 分区，重建创建alter table NAME add partition P_DEFAULT 分区.这时再去创建分区时，其子分区是按照新的subpartition template 来创建的。