1、按月創(chuàng)建分區(qū)
create table t_mhdp
(
id varchar2(50) not null,
flt_aircode varchar2(4),
flt_number number(7),
flt_date date
) partition by range (flt_date)
(
partition part201105 values less than (to_date(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
tablespace ts_mhdp_1105
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
next 1m
minextents 1
maxextents unlimited
),
partition part201106 values less than (to_date(' 2011-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
tablespace ts_mhdp_1106
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
next 1m
minextents 1
maxextents unlimited
),
partition part201107 values less than (to_date(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
tablespace ts_mhdp_1107
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
next 1m
minextents 1
maxextents unlimited
),
partition partdefault values less than (to_date(' 9999-12-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
tablespace ts_mhdp
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64k
next 1m
minextents 1
maxextents unlimited
)
);
2、創(chuàng)建全局索引
create index index_mhdp_pk on t_mhdp(id)
tablespace ts_index_mhdp
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64k
next 1m
minextents 1
maxextents unlimited
);
3、創(chuàng)建本地索引
create index index_mhdp_pk on t_mhdp(id) local (
partition part201105 tablespace ts_mhdp_index_1105,
partition part201106 tablespace ts_mhdp_index_1106,
partition part201107 tablespace ts_mhdp_index_1107,
partition partdefault tablespace ts_mhdp_index
);