创建子分区模板及使用subpartitions 方法快速添加子分区

原文链接：http://blog.csdn.net/hj402555749/article/details/12097025

一、使用模板创建子分区

/*drop table T_LOCRECORD_HJ purge ;*/

create table T_LOCRECORD_HJ
(
  id            VARCHAR2(36) not null,
  device_id     VARCHAR2(256),
  latitude      FLOAT,
  longitude     FLOAT,
  jmx           VARCHAR2(20),
  jmy           VARCHAR2(20),
  speed         FLOAT,
  direction     FLOAT,
  height        FLOAT,
  distance      FLOAT,
  statllite_num NUMBER,
  gpstime       DATE,
  inputdate     DATE default SYSDATE not null,
  locate_type   VARCHAR2(1),
  coord_type    NUMBER,
  state         VARCHAR2(10),
  pos_desc      VARCHAR2(1000),
  obj_id        VARCHAR2(256),
  obj_type      VARCHAR2(20)
)
partition by range (INPUTDATE)
SUBPARTITION BY hash (device_id )
SUBPARTITION TEMPLATE
(
  subpartition p001  , 
  subpartition p002  , 
  subpartition p003  , 
  subpartition p004  , 
  subpartition p005  , 
  subpartition p006  , 
  subpartition p007  , 
  subpartition p008  , 
  subpartition p009  , 
  subpartition p010 
)
(
  partition PAR_2013_06_1 values less than (TO_DATE(' 2013-06-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_06_1 ,
  partition PAR_2013_06_2 values less than (TO_DATE(' 2013-06-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_06_2,
  partition PAR_2013_06_3 values less than (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_06_3,
  partition PAR_2013_07_1 values less than (TO_DATE(' 2013-07-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_07_1,
  partition PAR_2013_07_2 values less than (TO_DATE(' 2013-07-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_07_2,
  partition PAR_2013_07_3 values less than (TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_07_3
);

这种方法创建的分区，在下次添加分区的时候会自动按照模版创建子分区，只需要按照如下方法添加即可：

alter table T_LOCRECORD_HJ add partition par_2013_08_01  values less than (TO_DATE(' 2013-08-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  tablespace HIS_GPS_08_1 ; 

二、使用subpartition 的方法来创建子分区

/*drop table T_LOCRECORD_HJ purge ;*/

create table T_LOCRECORD_HJ
(
  id            VARCHAR2(36) not null,
  device_id     VARCHAR2(256),
  latitude      FLOAT,
  longitude     FLOAT,
  jmx           VARCHAR2(20),
  jmy           VARCHAR2(20),
  speed         FLOAT,
  direction     FLOAT,
  height        FLOAT,
  distance      FLOAT,
  statllite_num NUMBER,
  gpstime       DATE,
  inputdate     DATE default SYSDATE not null,
  locate_type   VARCHAR2(1),
  coord_type    NUMBER,
  state         VARCHAR2(10),
  pos_desc      VARCHAR2(1000),
  obj_id        VARCHAR2(256),
  obj_type      VARCHAR2(20)
)
partition by range (INPUTDATE)
SUBPARTITION BY hash (device_id )  subpartitions 100
(
  partition PAR_2013_06_1 values less than (TO_DATE(' 2013-06-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_06_1 ,
  partition PAR_2013_06_2 values less than (TO_DATE(' 2013-06-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_06_2,
  partition PAR_2013_06_3 values less than (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_06_3,
  partition PAR_2013_07_1 values less than (TO_DATE(' 2013-07-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_07_1,
  partition PAR_2013_07_2 values less than (TO_DATE(' 2013-07-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_07_2,
  partition PAR_2013_07_3 values less than (TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace HIS_GPS_07_3
);

这种方法创建子分区的好处就是在初次创建分区的时候不用手动的去指点复杂的子分区规则，dbms 会根据subpartitions 后面的值创建出相应

个数的子分区。

这种方法的弊端就是在每次添加分区的时候，需要在每一个语句后面都要带上 subpatitions number  这样的参数。如：

alter table T_LOCRECORD_HJ130818 add partition  PAR_2013_09_1 values  less than (TO_DATE(' 2013-09-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))    tablespace HIS_GPS_09_1  subpartitions 100 ; 

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

list  子分区例子：

create table T_ORDER_COMMAND_XL
(
  id                   VARCHAR2(100) not null,
  command_type         NUMBER(4) not null,
  create_time          TIMESTAMP(6) not null,
  order_logistics_code VARCHAR2(100),
  order_channel_code   VARCHAR2(20),
  status               NUMBER(4) not null,
  is_offline           NUMBER(1),
  command_content      CLOB not null
)
partition by range (CREATE_TIME)
subpartition by list (COMMAND_TYPE)
SUBPARTITION TEMPLATE
(
    subpartition PAR_1001_10 values (10) tablespace TS_ORD,
    subpartition PAR_1001_DFT values (DEFAULT) tablespace TS_ORD
)
(
  partition PAR_2013_10_1 values less than (TO_DATE(' 2013-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TS_ORD ,
  partition PAR_2013_10_2 values less than (TO_DATE(' 2013-10-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TS_ORD
);


alter table T_ORD_ORDER_COMMAND_XL add partition  PAR_2013_10_3 values less than (TO_DATE(' 2013-10-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TS_ORD ;