Re: Paritioning Query

"Craig & Co." <crb_at_amsa.gov.au> wrote in message news:<410051e1$0$51356$c30e37c6_at_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.

Apparently your main problem is you don't want to type the year in your partition definition. And just because of this you consider the date field into 3 separate values. That would be the most excessive measure I ever saw to avoid typing the year. The 'other ideas' you request are quite obvious: if you want one partition per month you have to take the year into account.

--
Sybrand Bakker
Senior Oracle DBA