11g分布表新特性——Interval分区

网页链接:

http://blog.itpub.net/17203031/viewspace-706173/

附上建表语句:

　　

CREATE TABLE EDB.EDRQ_EVENT_NEW
(
PANELID VARCHAR2(13 BYTE) NOT NULL,
TIMEKEY VARCHAR2(17 BYTE) NOT NULL,
EVENTTIME DATE,
EVENTNAME VARCHAR2(20 BYTE),
DIMENSIONALCODE VARCHAR2(40 BYTE),
OLDPANELTYPE VARCHAR2(40 BYTE),
PANELTYPE VARCHAR2(40 BYTE),
OLDFGCODE VARCHAR2(50 BYTE),
FGCODE VARCHAR2(50 BYTE),
OLDFGCODEVERSION VARCHAR2(4 BYTE),
FGCODEVERSION VARCHAR2(4 BYTE),
OLDFACTORYNAME VARCHAR2(10 BYTE),
FACTORYNAME VARCHAR2(10 BYTE),
OLDDESTINATIONFACTORYNAME VARCHAR2(40 BYTE),
DESTINATIONFACTORY VARCHAR2(40 BYTE),
OLDLINE VARCHAR2(20 BYTE),
LINE VARCHAR2(30 BYTE),
PANELSTATE VARCHAR2(40 BYTE),
PANELHOLDSTATE VARCHAR2(40 BYTE),
EVENTUSER VARCHAR2(20 BYTE),
EVENTCOMMENT VARCHAR2(250 BYTE),
LASTPROCESSINGTIME DATE,
LASTPROCESSINGUSER VARCHAR2(20 BYTE),
LASTIDLETIME DATE,
LASTIDLEUSER VARCHAR2(20 BYTE),
REASONCODETYPE VARCHAR2(40 BYTE),
REASONCODE VARCHAR2(20 BYTE),
OLDPROCESSFLOWNAME VARCHAR2(10 BYTE),
PROCESSFLOWNAME VARCHAR2(10 BYTE),
OLDPROCESSFLOWVERSION VARCHAR2(4 BYTE),
PROCESSFLOWVERSION VARCHAR2(4 BYTE),
OLDPROCESSOPERATIONNAME VARCHAR2(40 BYTE),
PROCESSOPERATIONNAME VARCHAR2(40 BYTE),
OLDINBOXID VARCHAR2(30 BYTE),
INBOXID VARCHAR2(40 BYTE),
OLDPROCESSOPERATIONVERSION VARCHAR2(4 BYTE),
PROCESSOPERATIONVERSION VARCHAR2(4 BYTE),
NODESTACK VARCHAR2(40 BYTE),
MACHINENAME VARCHAR2(30 BYTE),
MACHINERECIPENAME VARCHAR2(400 BYTE),
REWORKSTATE VARCHAR2(40 BYTE),
REWORKCOUNT VARCHAR2(40 BYTE),
REWORKNODEID VARCHAR2(40 BYTE),
SYSTEMTIME DATE,
FROMSITE VARCHAR2(40 BYTE),
CELLLOTNAME VARCHAR2(17 BYTE),
CELLLOTTYPE VARCHAR2(40 BYTE),
FGKIND VARCHAR2(40 BYTE),
AGING VARCHAR2(40 BYTE),
MODULEID VARCHAR2(50 BYTE),
GROUPID VARCHAR2(40 BYTE),
GRADE VARCHAR2(4 BYTE),
REVISIONCODE VARCHAR2(4 BYTE),
CHECKINCODE VARCHAR2(3 BYTE),
REWORKSTARTSTEP VARCHAR2(40 BYTE),
FAANALYTICALTIME DATE,
RTINVOICE VARCHAR2(40 BYTE),
QAJUDGE VARCHAR2(40 BYTE),
LOTJUDGE VARCHAR2(40 BYTE),
DEPTCODE VARCHAR2(40 BYTE),
PPID VARCHAR2(40 BYTE),
OQALOTID VARCHAR2(16 BYTE),
FILOTID VARCHAR2(16 BYTE),
WORKORDER VARCHAR2(40 BYTE),
OLDWORKORDER VARCHAR2(40 BYTE),
OQCCOUNT NUMBER,
OQCLOTOUTCOME VARCHAR2(10 BYTE),
FGCODESTATE VARCHAR2(160 BYTE),
FGCODEHOLDSTATE VARCHAR2(160 BYTE),
PRINCIPAL VARCHAR2(30 BYTE),
BLUID VARCHAR2(50 BYTE),
FPCID VARCHAR2(50 BYTE),
BOARDID VARCHAR2(50 BYTE),
POLUPID VARCHAR2(50 BYTE),
POLTOPID VARCHAR2(50 BYTE),
CGID VARCHAR2(50 BYTE),
ICID VARCHAR2(50 BYTE),
GLASSID VARCHAR2(50 BYTE),
PCBID VARCHAR2(50 BYTE),
ACFID VARCHAR2(50 BYTE),
SHIFT VARCHAR2(40 BYTE)
)
NOCOMPRESS
TABLESPACE MES_BASIC_DATA
PCTUSED 0
PCTFREE 10
INITRANS 20
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 8K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (EVENTTIME)
INTERVAL( NUMTODSINTERVAL(7,'DAY')) STORE IN (MES_PANELMAT_DATA1,MES_PANELMAT_DATA3,MES_PANELMAT_DATA2,MES_PANELMAT_DATA4,MES_PANELHIS_INDEX)
(
PARTITION PANELHISTORY_01 VALUES LESS THAN (TO_DATE(' 2019-05-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS
TABLESPACE MES_PANELMAT_DATA1
PCTFREE 10
INITRANS 20
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 8K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCACHE
MONITORING;

COMMENT ON TABLE EDB.EDRQ_EVENT_NEW IS 'panel历史表，加工履历查询';

CREATE INDEX EDB.EDRQ_UN_FH_CODE ON EDB.EDRQ_EVENT_NEW
(OLDPROCESSOPERATIONNAME)
LOGGING
TABLESPACE MES_PANELHIS_INDEX
PCTFREE 10
INITRANS 30
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 8K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX EDB.EDRQ_UN_PH_EVENTNAME ON EDB.EDRQ_EVENT_NEW
(EVENTNAME)
LOGGING
TABLESPACE MES_PANELHIS_INDEX
PCTFREE 10
INITRANS 30
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 8K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX EDB.EDRQ_UN_PH_FGCODE ON EDB.EDRQ_EVENT_NEW
(FGCODE)
LOGGING
TABLESPACE MES_PANELHIS_INDEX
PCTFREE 10
INITRANS 30
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 8K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX EDB.EDRQ_UN_PH_PON ON EDB.EDRQ_EVENT_NEW
(PROCESSOPERATIONNAME)
LOGGING
TABLESPACE MES_PANELHIS_INDEX
PCTFREE 10
INITRANS 30
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 8K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

CREATE INDEX EDB.EDRQ_UN_PH_TIMEKEY ON EDB.EDRQ_EVENT_NEW
(TIMEKEY)
LOGGING
TABLESPACE MES_PANELHIS_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);

ALTER TABLE EDB.EDRQ_EVENT_NEW ADD (
PRIMARY KEY
(PANELID, TIMEKEY)
USING INDEX
TABLESPACE MES_PANELHIS_INDEX
PCTFREE 10
INITRANS 30
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 8K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
ENABLE VALIDATE);