Home » RDBMS Server » Server Administration » Partition
Partition [message #299918] Wed, 13 February 2008 12:29 Go to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
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
);
Re: Partition [message #299920 is a reply to message #299918] Wed, 13 February 2008 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there anything else i need to look

Maybe OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Also post your Oracle version (4 decimals).

Regards
Michel
Re: Partition [message #299928 is a reply to message #299920] Wed, 13 February 2008 13:15 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
We are using Oracle 10.2.0.2 and following are the statements again:

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
 );


I will use the following to partition:

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
 );

Re: Partition [message #299932 is a reply to message #299928] Wed, 13 February 2008 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there anything else i need to look at before i partition?

You have to clearly know why you have to (not want) partition.

Regards
Michel
Re: Partition [message #299935 is a reply to message #299932] Wed, 13 February 2008 14:24 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
I know why I wnat to partition, but my question is, is my approach is right or not?
Re: Partition [message #299937 is a reply to message #299935] Wed, 13 February 2008 14:59 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Right regarding what?
Is the syntax valid? Yes, so it is alright. No, it is not.

Regards
Michel
Previous Topic: changing host name problem
Next Topic: Low open file descriptor
Goto Forum:
  


Current Time: Fri Dec 09 03:50:33 CST 2016

Total time taken to generate the page: 0.06640 seconds