Re: Partition

caprikar_at_gmail.com wrote:
> Hi I have the following table which is not partitioned and I want to
> partition it, here is the method am using to partition that table, can
> anyone tell me is it the right way to do or is there anything else i
> need to look at before i partition?
>
> Current status:
>
> CREATE TABLE CHANGE_REC
> (
> CHANGE_REC_ID NUMBER(15) NOT NULL,
> CHANGE_LOG_ID NUMBER(15),
> TAB_NAME VARCHAR2(30 BYTE),
> COL_NAME VARCHAR2(30 BYTE),
> OLD_VALUE VARCHAR2(4000 BYTE),
> NEW_VALUE VARCHAR2(4000 BYTE),
> CHNGE_TIME TIMESTAMP(6)
> )
> TABLESPACE DATA
> PCTUSED 40
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> LOGGING
> NOCOMPRESS
> NOCACHE
> NOPARALLEL
> MONITORING;
>
> CREATE UNIQUE INDEX PK_CHANGE_REC ON CHANGE_REC
> (CHANGE_REC_ID)
> LOGGING
> TABLESPACE INDEX
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> PCTINCREASE 0
> BUFFER_POOL DEFAULT
> )
> NOPARALLEL;
>
> ALTER TABLE CHANGE_REC ADD (
> CONSTRAINT PK_CHANGE_REC
> PRIMARY KEY
> (CHANGE_REC_ID)
> USING INDEX
> TABLESPACE INDEX
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> );
>
> ---------------------------------------------------------
>
> Want to partition that table using..
>
> CREATE TABLE CHANGE_REC
> (
> CHANGE_REC_ID NUMBER(15) NOT NULL,
> CHANGE_LOG_ID NUMBER(15),
> TABLE_NAME VARCHAR2(30 BYTE),
> COL_NAME VARCHAR2(30 BYTE),
> OLD_VALUE VARCHAR2(4000 BYTE),
> NEW_VALUE VARCHAR2(4000 BYTE),
> CHNGE_TIME TIMESTAMP(6)
> )
> TABLESPACE DATA
> LOGGING
> PARTITION BY RANGE (CHNGE_TIME)
> (
> PARTITION Y08M01 VALUES LESS THAN (TIMESTAMP'2008-02-01 00:00:00')
> LOGGING
> NOCOMPRESS
> TABLESPACE DATA,
>
> PARTITION Y08M02 VALUES LESS THAN (TIMESTAMP'2008-03-01 00:00:00')
> LOGGING
> NOCOMPRESS
> TABLESPACE DATA,
>
> PARTITION Y08M03 VALUES LESS THAN (TIMESTAMP'2008-04-01 00:00:00')
> LOGGING
> NOCOMPRESS
> TABLESPACE DATA
> )
> NOCOMPRESS
> NOCACHE
> NOPARALLEL
> MONITORING;
>
>
> CREATE UNIQUE INDEX PK_CHANGE_REC ON CHANGE_REC
> (CHANGE_LOG_ID, CHNGE_TIME)
> TABLESPACE INDEX
> INITRANS 2
> MAXTRANS 255
> LOGGING
> LOCAL (
> PARTITION Y08M01
> LOGGING
> NOCOMPRESS
> TABLESPACE INDEX,
>
> PARTITION Y08M02
> LOGGING
> NOCOMPRESS
> TABLESPACE INDEX,
>
> PARTITION Y08M03
> LOGGING
> NOCOMPRESS
> TABLESPACE INDEX
> )
> NOPARALLEL;
>
>
> ALTER TABLE CHANGE_REC ADD (
> CONSTRAINT PK_CHANGE_REC
> PRIMARY KEY
> (CHANGE_REC_ID, CHNGE_TIME)
> USING INDEX
> TABLESPACE USERS
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> );

seems reasonable. Unless you plan on maintaining this thing monthly by adding the next months partition - I would consider building the tablespaces to necessary for the amount of data you want to save - say 18 months or whatever your retention policy may be... and/or make sure you document your month-end procedures to add "next month" so that the DBA that comes after you knows what you did... Received on Wed Feb 13 2008 - 21:39:06 CST