Monday, January 17, 2011

Oracle partitioning table

Just a note on how we partitioned a large table (Transaction) in Oracle into monthly partitions/. It involves creating a swap table to store existing data, then placing it into seperate partitions.


Just a note on how we partitioned a large table (Transaction) in Oracle into monthly partitions/.

DROP TABLE TRANSACTION_SWAP
/
ALTER TABLE TRANSACTION DROP CONSTRAINT TRANSACTION_PK
/
DROP INDEX TRANSACTION_IDX1
/
DROP INDEX TRANSACTION_IDX2
/
RENAME TRANSACTION TO TRANSACTION_SWAP
/
REM
REM Now create new version of table, partitioned and with a different primary key
REM

CREATE TABLE TRANSACTION (
"ID" NUMBER NOT NULL ENABLE,
"ORIGINATOR" VARCHAR2(4 BYTE),
"ORIGINATOR_INSTANCE_ID" VARCHAR2(100 BYTE),
"MESSAGE_REF" VARCHAR2(512 BYTE),
"ENTITY_REF" VARCHAR2(512 BYTE),
"TIME" TIMESTAMP(6),
"STATUS" VARCHAR2(5 BYTE),
"JMS_MESSAGE_ID" VARCHAR2(30 BYTE),
"PROJECT_NAME" VARCHAR2(384 BYTE),
"OPERATION" VARCHAR2(150 BYTE),
"INTERFACE" VARCHAR2(20 BYTE))
Partition By Range(Time)
(Partition P_2010_10_31 Values Less Than (To_Timestamp('2010-11-01','YYYY-MM-DD')),
Partition P_2010_11_30 Values Less Than (To_Timestamp('2010-12-01','YYYY-MM-DD')),
Partition P_2010_12_31 Values Less Than (To_Timestamp('2011-01-01','YYYY-MM-DD')),
Partition P_2011_01_31 Values Less Than (To_Timestamp('2011-02-01','YYYY-MM-DD')),
Partition P_High Values Less Than(Maxvalue));
/*
REM
REM Copy over all data before creating indexes - for efficiency reasons
REM Note in some environments, the time can be null on some entries in the table.
REM
REM These rows will be placed in a distinct table
REM

INSERT INTO TRANSACTION
(ID
,ORIGINATOR
,ORIGINATOR_INSTANCE_ID
,MESSAGE_REF
,ENTITY_REF
,TIME
,STATUS
,JMS_MESSAGE_ID
,PROJECT_NAME
,OPERATION
,INTERFACE)
SELECT
ID
,ORIGINATOR
,ORIGINATOR_INSTANCE_ID
,MESSAGE_REF
,ENTITY_REF
,TIME
,STATUS
,JMS_MESSAGE_ID
,PROJECT_NAME
,OPERATION
,INTERFACE
FROM TRANSACTION_SWAP
WHERE TIME IS NOT NULL
/
DROP TABLE TRANSACTION_ARC_NOTIME
/
CREATE TABLE TRANSACTION_ARC_NOTIME
AS
SELECT *
FROM TRANSACTION_SWAP
WHERE TIME IS NULL
/
/*
REM
REM Now create indexes
REM
*/
rem ALTER TABLE TRANSACTION ADD (CONSTRAINT "TRANSACTION_PK" PRIMARY KEY (TIME,ID));
CREATE UNIQUE INDEX TRANSACTION_UID1 ON TRANSACTION(TIME,ID) LOCAL;

ALTER TABLE TRANSACTION ADD (CONSTRAINT TRANSACTION_PK PRIMARY KEY (TIME, ID) USING INDEX TRANSACTION_UID1);

CREATE INDEX TRANSACTION_IDX1 On TRANSACTION (ORIGINATOR_INSTANCE_ID, STATUS) LOCAL;
REM
REM And re-create a grant which is present on production. This may fail elsewhere
REM
GRANT SELECT ON TRANSACTION TO READONLY
/

REM
REM Create the archive table at the same time
REM
CREATE TABLE TRANSACTION_ARC (
"ID" NUMBER NOT NULL ENABLE,
"ORIGINATOR" VARCHAR2(4 BYTE),
"ORIGINATOR_INSTANCE_ID" VARCHAR2(100 BYTE),
"MESSAGE_REF" VARCHAR2(512 BYTE),
"ENTITY_REF" VARCHAR2(512 BYTE),
"TIME" TIMESTAMP(6),
"STATUS" VARCHAR2(5 BYTE),
"JMS_MESSAGE_ID" VARCHAR2(30 BYTE),
"PROJECT_NAME" VARCHAR2(384 BYTE),
"OPERATION" VARCHAR2(150 BYTE),
"INTERFACE" VARCHAR2(20 BYTE))
Partition By Range(Time)
(Partition P_2010_10_31 Values Less Than (To_Timestamp('2010-11-01','YYYY-MM-DD')),
Partition P_2010_11_30 Values Less Than (To_Timestamp('2010-12-01','YYYY-MM-DD')),
Partition P_2010_12_31 Values Less Than (To_Timestamp('2011-01-01','YYYY-MM-DD')),
Partition P_2011_01_31 Values Less Than (To_Timestamp('2011-02-01','YYYY-MM-DD')),
Partition P_High Values Less Than(Maxvalue));

REM
REM recreate any package body with a new version which defaults the time in the table,
REM to make sure there is always a value there
REM
@TrackingProcedureBody

PROMPT *** OPERATION COMPLETE ***
PROMPT
PROMPT *** WHEN HAPPY WITH RESULTS, TRUNCATE THE TABLE TRANSACTION_SWAP

No comments: