Paritioning Query

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Fri, 23 Jul 2004 09:46:34 +1000
Message-ID: <410051e1$0$51356$c30e37c6@lon-reader.news.telstra.net>

Hi Everyone,

Firstly - Oracle 8.1.7.4 - Solaris 8.

Secondly,
I have a table which records audit information.

Currently every 14 days I have a procedure which selects all records older than
one year ago, gets each row, puts the row in a CSV file, then deletes the row.
Understandably this creates a huge load on the database.

I am looking to partition the table into 12 months without changing the structure of the AUDIT_LOG table, which is

AUDIT_ID                                   NOT NULL     NUMBER
OBJECT_NAME                         NOT NULL     VARCHAR2(50)
OBJECT_PRIMARY_KEY                                  VARCHAR2(100)
OBJECT_PRIMARY_KEY_2                              VARCHAR2(100)
OBJECT_PRIMARY_KEY_3                              VARCHAR2(100)

ATTRIBUTE
VARCHAR2(30)

PRE_CHANGE_VALUE                                      VARCHAR2(4000)
POST_CHANGE_VALUE                                    VARCHAR2(4000)
CHANGED_BY                                                    VARCHAR2(30)
CHANGED_DATETIME                                      DATE
APPLICATION_OR_PROCESS                          VARCHAR2(115)

ACTION
VARCHAR2(10) I was looking to export and drop the table above, then recreate it as follows
CREATE TABLE AUDIT_LOG
(

AUDIT_ID                                      NUMBER,
OBJECT_NAME                            VARCHAR2(40)    NOT NULL,
OBJECT_PRIMARY_KEY            VARCHAR2(100),
OBJECT_PRIMARY_KEY_2        VARCHAR2(100),
OBJECT_PRIMARY_KEY_3        VARCHAR2(100),
ATTRIBUTE                                   VARCHAR2(30),
PRE_CHANGE_VALUE                VARCHAR2(4000),
POST_CHANGED_VALUE          VARCHAR2(4000),
CHANGED_BY                             VARCHAR2(30),
CHANGED_DATETIME               DATE,
APPLICATION_OR_PROCESS   VARCHAR2(115),
ACTION                                        VARCHAR2(10)

)
TABLESPACE USER_TBL
PARTITION BY RANGE (CHANGED_DATETIME)
(

PARTITION JAN_AUD VALUES LESS THAN (TO_CHAR('01-FEB')) TABLESPACE AUDIT_Q1,
PARTITION FEB_AUD VALUES LESS THAN (TO_CHAR('01-MAR')) TABLESPACE AUDIT_Q1
PARTITION MAR_AUD VALUES LESS THAN (TO_CHAR('01-APR')) TABLESPACE AUDIT_Q1
PARTITION APR_AUD VALUES LESS THAN (TO_CHAR('01-MAY')) TABLESPACE AUDIT_Q2
PARTITION MAY_AUD VALUES LESS THAN (TO_CHAR('01-JUN'))

..........................

PARTITION NOV_AUD VALUES LESS THAN (TO_CHAR('01-DEC')) TABLESPACE AUDIT_Q4
PARTITION DEC_AUD VALUES LESS THAN (MAXVALUE) TABLESPACE AUDIT_Q4
)
But since Y2K Oracle needs a full year, I had to change my thoughts.

And this is where I can't get enough information on partitioning date fields to make a decision on how to partition the table.

Is it possible to still created the above partitions, i.e. FEB_AUD, etc. PARTITION FEB_AUD VALUES LESS THAN (TO_CHAR('01-MAR-2004')) TABLESPACE AUDIT_Q1
Fast forward 11 months.
On the 28th January, is it possible to drop the FEB_AUD partition, then recreate it so the
value field is 01-MAR-2005.

I'm thinking the syntax would be similar to ALTER TABLE AUDIT_LOG
DROP PARTITION FEB_AUD; ALTER TABLE AUDIT_LOG
ADD PARTITION FEB_AUD VALUES LESS THAN ('01-MAR-2005') Would this put ALL records less than 01-MAR-2005 in that partition? As I would still like to keep the 01-APR-2004 information in the APR_AUD partition.

Any other ideas, apart from separating the Date field into 3 separate values?

Thank you in advance.
Craig

Craig Burtenshaw
Unix System/Oracle Database Administrator Australian Maritime Safety Authority. Received on Thu Jul 22 2004 - 18:46:34 CDT