Oracle分区、索引sql脚本

--创建月分区

create table DW_UNICOM_ACCESS_BASE_T

(
  ID             NUMBER(16) not null,
  BUSINESS_DATE  VARCHAR2(16) not null,
  AREA_ID        NUMBER(16) default 0,
  AREA           VARCHAR2(100),
  ACCESS_TYPE_ID NUMBER(16) default 0,
  ACCESS_TYPE    VARCHAR2(100),
  PV             NUMBER(20) default 0,
  PV_INDEX       NUMBER(20) default 0,
  UV             NUMBER(20) default 0,
  STATE          CHAR(2) default '0' not null
)


PARTITION BY Range(BUSINESS_DATE)
(
PARTITION UNICOM_ACCESS_201201 VALUES LESS THAN ('20120201'),
PARTITION UNICOM_ACCESS_201202 VALUES LESS THAN ('20120301'),
PARTITION UNICOM_ACCESS_201203 VALUES LESS THAN ('20120401'),
PARTITION UNICOM_ACCESS_201204 VALUES LESS THAN ('20120501'),
PARTITION UNICOM_ACCESS_201205 VALUES LESS THAN ('20120601'),
PARTITION UNICOM_ACCESS_201206 VALUES LESS THAN ('20120701'),
PARTITION UNICOM_ACCESS_201207 VALUES LESS THAN ('20120801'),
PARTITION UNICOM_ACCESS_201208 VALUES LESS THAN ('20120901'),
PARTITION UNICOM_ACCESS_201209 VALUES LESS THAN ('20121001'),
PARTITION UNICOM_ACCESS_201210 VALUES LESS THAN ('20121101'),
PARTITION UNICOM_ACCESS_201211 VALUES LESS THAN ('20121201'),
PARTITION UNICOM_ACCESS_201212 VALUES LESS THAN ('20130101'),


PARTITION UNICOM_ACCESS_201301 VALUES LESS THAN ('20130201'),
PARTITION UNICOM_ACCESS_201302 VALUES LESS THAN ('20130301'),
PARTITION UNICOM_ACCESS_201303 VALUES LESS THAN ('20130401'),
PARTITION UNICOM_ACCESS_201304 VALUES LESS THAN ('20130501'),
PARTITION UNICOM_ACCESS_201305 VALUES LESS THAN ('20130601'),
PARTITION UNICOM_ACCESS_201306 VALUES LESS THAN ('20130701'),
PARTITION UNICOM_ACCESS_201307 VALUES LESS THAN ('20130801'),
PARTITION UNICOM_ACCESS_201308 VALUES LESS THAN ('20130901'),
PARTITION UNICOM_ACCESS_201309 VALUES LESS THAN ('20131001'),
PARTITION UNICOM_ACCESS_201310 VALUES LESS THAN ('20131101'),
PARTITION UNICOM_ACCESS_201311 VALUES LESS THAN ('20131201'),
PARTITION UNICOM_ACCESS_201312 VALUES LESS THAN ('20140101'),


PARTITION UNICOM_ACCESS_MAXVALUE VALUES LESS THAN (MAXVALUE)

);

--创建索引

Drop Index INX_UNI_ACC_ACCESS_TYPE_GP;
CREATE INDEX INX_UNI_ACC_ACCESS_TYPE_GP
ON dw_unicom_access_base_t(ACCESS_TYPE) Global
partition by range(ACCESS_TYPE)
(
PARTITION UNI_ACC_ACCESS_TYPE_201201 VALUES LESS THAN ('20120201'),
PARTITION UNI_ACC_ACCESS_TYPE_201202 VALUES LESS THAN ('20120301'),
PARTITION UNI_ACC_ACCESS_TYPE_201203 VALUES LESS THAN ('20120401'),
PARTITION UNI_ACC_ACCESS_TYPE_201204 VALUES LESS THAN ('20120501'),
PARTITION UNI_ACC_ACCESS_TYPE_201205 VALUES LESS THAN ('20120601'),
PARTITION UNI_ACC_ACCESS_TYPE_201206 VALUES LESS THAN ('20120701'),
PARTITION UNI_ACC_ACCESS_TYPE_201207 VALUES LESS THAN ('20120801'),
PARTITION UNI_ACC_ACCESS_TYPE_201208 VALUES LESS THAN ('20120901'),
PARTITION UNI_ACC_ACCESS_TYPE_201209 VALUES LESS THAN ('20121001'),
PARTITION UNI_ACC_ACCESS_TYPE_201210 VALUES LESS THAN ('20121101'),
PARTITION UNI_ACC_ACCESS_TYPE_201211 VALUES LESS THAN ('20121201'),
PARTITION UNI_ACC_ACCESS_TYPE_201212 VALUES LESS THAN ('20130101'),
PARTITION UNI_ACC_ACCESS_TYPE_201301 VALUES LESS THAN ('20130201'),
PARTITION UNI_ACC_ACCESS_TYPE_201302 VALUES LESS THAN ('20130301'),
PARTITION UNI_ACC_ACCESS_TYPE_201303 VALUES LESS THAN ('20130401'),
PARTITION UNI_ACC_ACCESS_TYPE_201304 VALUES LESS THAN ('20130501'),
PARTITION UNI_ACC_ACCESS_TYPE_201305 VALUES LESS THAN ('20130601'),
PARTITION UNI_ACC_ACCESS_TYPE_201306 VALUES LESS THAN ('20130701'),
PARTITION UNI_ACC_ACCESS_TYPE_201307 VALUES LESS THAN ('20130801'),
PARTITION UNI_ACC_ACCESS_TYPE_201308 VALUES LESS THAN ('20130901'),
PARTITION UNI_ACC_ACCESS_TYPE_201309 VALUES LESS THAN ('20131001'),
PARTITION UNI_ACC_ACCESS_TYPE_201310 VALUES LESS THAN ('20131101'),
PARTITION UNI_ACC_ACCESS_TYPE_201311 VALUES LESS THAN ('20131201'),
PARTITION UNI_ACC_ACCESS_TYPE_201312 VALUES LESS THAN ('20140101'),
PARTITION UNI_ACC_ACCESS_TYPE_MAXVALUE VALUES LESS THAN (MAXVALUE)
);