Technical data

Partitioning Recommendations for Oracle Utilities Meter Data Management D-5
D1_MSRMT_LOG
CREATE TABLE D1_MSRMT_LOG
(
MEASR_COMP_ID CHAR(12 BYTE),
MSRMT_DTTM DATE,
SEQNO NUMBER(5,0),
ORIG_INIT_MSRMT_ID CHAR(14 BYTE) DEFAULT ' ' NOT NULL ENABLE,
BUS_OBJ_CD CHAR(30 BYTE) DEFAULT ' ' NOT NULL ENABLE,
BO_DATA_AREA CLOB,
CHAR_TYPE_CD CHAR(8 BYTE) DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL CHAR(16 BYTE) DEFAULT ' ' NOT NULL ENABLE,
ADHOC_CHAR_VAL VARCHAR2(254 BYTE) DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK1 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK2 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK3 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK4 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,
CHAR_VAL_FK5 VARCHAR2(50 BYTE) DEFAULT ' ' NOT NULL ENABLE,
DESCRLONG VARCHAR2(4000 BYTE) DEFAULT ' ' NOT NULL ENABLE,
LOG_DTTM DATE NOT NULL ENABLE,
MESSAGE_CAT_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,
MESSAGE_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,
USER_ID CHAR(8 BYTE) DEFAULT ' ' NOT
NULL ENABLE,
VERSION NUMBER(5,0) DEFAULT 1 NOT NULL
ENABLE,
MSRMT_LOG_ENTRY_TYPE_FLG CHAR(4 BYTE) DEFAULT ' ' NOT NULL ENABLE
)
TABLESPACE <Tablespace_Name>
ENABLE ROW MOVEMENT
PARTITION BY RANGE (MSRMT_DTTM)
SUBPARTITION BY range (MEASR_COMP_ID)
SUBPARTITION TEMPLATE(
subpartition SUB1 values less than (124999999999),
subpartition SUB2 values less than (249999999999),
subpartition SUB3 values less than (374999999999),
subpartition SUB4 values less than (499999999999),
subpartition SUB5 values less than (624999999999),
subpartition SUB6 values less than (744999999999),
subpartition SUB7 values less than (874999999999),
subpartition SUB8 values less than (maxvalue)
)
(PARTITION P1 VALUES LESS THAN(TO_DATE('15/12/2010 00:00:01','DD/MM/
YYYY HH24:MI:SS')),
PARTITION P2 VALUES LESS THAN(TO_DATE('01/01/2011 00:00:01','DD/MM/
YYYY HH24:MI:SS')),
PARTITION P3 VALUES LESS THAN(TO_DATE('15/01/2011 00:00:01','DD/MM/
YYYY HH24:MI:SS')),
PARTITION P4 VALUES LESS THAN(TO_DATE('01/02/2011 00:00:01','DD/MM/
YYYY HH24:MI:SS')),
PARTITION P5 VALUES LESS THAN(TO_DATE('15/02/2011 00:00:01','DD/MM/
YYYY HH24:MI:SS')),
PARTITION P6 VALUES LESS THAN(TO_DATE('01/03/2011 00:00:01','DD/MM/
YYYY HH24:MI:SS')),
PARTITION P7 VALUES LESS THAN(TO_DATE('15/03/2011 00:00:01','DD/MM/
YYYY HH24:MI:SS'))
);
CREATE UNIQUE INDEX D1T300P0 ON D1_MSRMT_LOG
(
MEASR_COMP_ID, MSRMT_DTTM, SEQNO
) TABLESPACE <Tablespace_Name> local COMPRESS 1;