转:http://blog.csdn.net/hj402555749/article/details/12097025
版权声明:本文为博主原创文章,未经博主允许不得转载。
一、使用模板创建子分区
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 ;
/*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
);
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 后面的值创建出相应
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 ;
(
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 ;