博客

Oracle根据时间字段创建分区表并对之前的历史数据进行分区迁移

CREATE TABLE USER_INFOR_MESSAGE 
( 
 "IDNUMBER" NUMBER NOT NULL ,
 "MESSAGETYPE" NVARCHAR2(10) NULL ,
 "ACCNO" NVARCHAR2(32) NULL ,
 "CARDNO" NVARCHAR2(64) NULL ,
 "CARDID" NVARCHAR2(64) NULL ,
 "OLDCARDNO" NVARCHAR2(64) NULL ,
 "OLDCARDID" NVARCHAR2(64) NULL ,
 "CDTYPE" NVARCHAR2(32) NULL ,
 "USERNAME" NVARCHAR2(100) NULL ,
 "IDTYPE" NVARCHAR2(16) NULL ,
 "IDSERIAL" NVARCHAR2(64) NULL ,
 "PID" NVARCHAR2(32) NULL ,
 "DEPTSTR" NVARCHAR2(32) NULL ,
 "CTRCODE" NVARCHAR2(16) NULL ,
 "NATCODE" NVARCHAR2(16) NULL ,
 "SEX" NVARCHAR2(10) NULL ,
 "BIRTHDAY" NVARCHAR2(16) NULL ,
 "INSCHOOL" NVARCHAR2(16) NULL ,
 "JOBCODE" NVARCHAR2(16) NULL ,
 "RECTYPE" NVARCHAR2(16) NULL ,
 "GRADE" NVARCHAR2(16) NULL ,
 "IDSERIAL1" NVARCHAR2(32) NULL ,
 "MODIFYDATE" NVARCHAR2(32) NULL ,
 "IDTYPE1" NVARCHAR2(16) NULL ,
 "EFFECTDATE" NVARCHAR2(32) NULL ,
 "RELATIONCARDNO" NVARCHAR2(32) NULL ,
 "RELATIONCARDID" NVARCHAR2(32) NULL ,
 "SCHOOLCODE" NVARCHAR2(16) NULL ,
 "ORGID" NVARCHAR2(16) NULL ,
 "REMARK" NVARCHAR2(16) NULL ,
 "REMARK1" NVARCHAR2(16) NULL ,
 "IDENTITYNO" NVARCHAR2(16) NULL ,
 "MODIFYDATETIME" DATE NULL ,
 "CREATETIME" DATE NULL ,
 "STATE" NVARCHAR2(10) NULL ,
 "IMPORT_REMARK" NVARCHAR2(255) NULL 
)
PARTITION BY RANGE (MODIFYDATE) INTERVAL ( NUMTOYMINTERVAL(1, 'month') )
(
 PARTITION LESS_2018 VALUES LESS THAN(TO_DATE('2018-01-01', 'yyyy-mm-dd'))
 PARTITION LESS_2019 VALUES LESS THAN(TO_DATE('2019-01-01', 'yyyy-mm-dd'))
 PARTITION LESS_2020 VALUES LESS THAN(TO_DATE('2020-01-01', 'yyyy-mm-dd'))
 PARTITION LESS_2021 VALUES LESS THAN(TO_DATE('2021-10-12', 'yyyy-mm-dd'))
)

单表数据2亿+ 指定月数据查询ms级