Partition management


Lets consider an example, you have a project to create a table to hold application AUDIT data. The data needs to be stored for 12 months, and after that, it should go into an archive table, which won’t really be accessed that much, but the data needs to be stored there for 7 years, as required by, lets say a some federal agency.

Similarly to other things in IT, there is more then one way to skin a cat, you can use transportable tablespaces, or you can dump the monthly table into a flat file, but the method that I like the best is PARTITION and PARTITION EXCHANGE clause.

In other words, you will have an APP_AUDIT table, which will have table partitions created dynamically every month. Lets say on the first of every month, it will create a partition that will be used the following month. Every partition will hold exactly one month of data. Therefore, the table will have 12 partitions at a time holding data for the past 12 months. Partitions older then 12 months will be moved into the APP_AUDIT_ARCHIVE table via exchange partition clause. So this whole project will become 3 (you will see why 3 in a second) tables, and a 10 line procedure which will be fired off every month via DBA_JOB or DBMS_SCHEDULER queue. The procedure will create a new partition, and move an existing partition into an ARCHIVE table. Lets table a look at the example.

--my original table
CREATE TABLE APP_AUDIT
(aaa NUMBER NOT NULL,
bbb DATE NOT NULL,
ccc VARCHAR2(500))
PARTITION BY RANGE (bbb)
(PARTITION APP_AUDIT_010108 VALUES LESS THAN (‘01/01/08’),
PARTITION APP_AUDIT_020108 VALUES LESS THAN (‘02/01/08’),
PARTITION APP_AUDIT_030108 VALUES LESS THAN (‘03/01/08’),
.
.
.
PARTITION AUDIT_APP_AUDIT_120108 VALUES LESS THAN (‘12/01/08’);

--my archive table
CREATE TABLE APP_AUDIT_ARCHIVE
(aaa NUMBER NOT NULL,
bbb DATE NOT NULL,
ccc VARCHAR2(500))
PARTITION BY RANGE (bbb)
(PARTITION APP_AUDIT_010105 VALUES LESS (‘01/01/05’),
.
.
.
(PARTITION APP_AUDIT_120107 VALUES LESS (‘12/01/07’),

--I need this temporary table in order for the EXCHANGE PARTITION clause to work. You
--cannot exchange partition between two partitioned tables, but you can exchange
-- partitions between a partitioned table and not partitioned table
CREATE TABLE APP_AUDIT_TEMP
(aaa NUMBER NOT NULL,
bbb DATE NOT NULL,
ccc VARCHAR2(500));

--doing the actual move operation. The whole thing took less then 5 seconds for a
--partition with 1 million rows

SQL> ALTER TABLE APP_AUDIT EXCHANGE PARTITION APP_AUDIT_010108
WITH TABLE APP_AUDIT_TEMP INCLUDING INDEXES WITH VALIDATION;

Table altered.

SQL> alter table APP_AUDIT_ARCHIVE add partition APP_AUDIT_010108
VALUES LESS THAN (‘01/01/08’),

Table altered.

SQL> ALTER TABLE APP_AUDIT_ARCHIVE EXCHANGE PARTITION APP_AUDIT_010108
WITH TABLE APP_AUDIT_TEMP INCLUDING INDEXES WITH VALIDATION;

Table altered.



And you are done. All you have to do is to add the above statements into a PLSQL procedure. You might have to come up with a little but of logic to generate the partition name using the current date
i.e.

execute immediate 'exec dbms_output.put_line ('alter table APP_AUDIT_ARCHIVE add partition APP_AUDIT_'||to_char(trunc(add_months(sysdate,1),'MM'),'mmddyy')||' VALUES LESS THAN '||to_char(trunc(add_months(sysdate,1),'MM'),'''mmddyy''') );

and you will have archiving working automatically without your monthly intervention.



Copyright © 2007, All rights reserved by RudnikConsulting Inc