Oracle Partition

One of the features that are sometimes overlooked in Oracle is partitioning. I found partitioning to be useful to simplify housekeeping of data that can be done based on date range.

I like to think of partition as table within a table. In fact, based on the documentations I read in SQL Server, that is how that is done. Create a table and then a view that refers to them. Personally, I like Oracle's implementation - each is so much easier to manage.

As for housekeeping, from experience deleting few thousand records will take 30 to 40 minutes but dropping the partition takes less than a minute. Some additional benefits of dropping the partition is that the space released by the partition is returned to the workspace and can be reused by any other table using the workspace. For deletion the space freed may not be reusable.

For example, I have a system that we extract high volume of transaction on an hourly basis for processing. This data is no longer needed after processing. In this instance we create a partition by date range:

CREATE TABLE LOTTXNTAB
(
LOTID VARCHAR2(20),
TXNTIME DATE,
EXTDATE DATE,
QTY NUMBER,
STEP VARCHAR2(20)
)
PARTITION BY RANGE(EXTDATE)
( PARTITION lottxntab0 VALUES LESS THAN(to_date('20070101','yyyymmdd')));


In another scenario where we have leave records that we upload for multiple departments but we still want to separate drop based on the date range. Also the data is obtained from separate data file and uploaded daily but we wish to retain only one copy for each week. We can then use the date as the partition key and deptnumber as the sub-partition key.

CREATE TABLE ELEAVETAB
(
EMPNO VARCHAR2(20),
TXNTIME DATE,
WEEKCUTOFF DATE,
LBALANCE NUMBER,
DEPTNO VARCHAR2(20)
)
PARTITION BY RANGE(WEEKCUTOFF)
SUBPARTITION BY LIST(DEPTNO) SUBPARTITION TEMPLATE
(
SUBPARTITION S1 VALUES('PNG')
, SUBPARTITION S2 VALUES('SNG')
)
(
PARTITION ELEAVETAB0 VALUES LESS THAN(to_date('20070101','yyyymmdd')),
PARTITION ELEAVETAB_1 VALUES LESS THAN(TO_DATE('20080201','YYYYMMDD'))
);

This way, I can update the data for each dept and then check to see if I can delete other data for the dept for the week before loading the new data then drop partition for both depts once the retention date has exceeded. Also, when the new partition is created, the subpartitions will also be automatically created.

One thing to note, then when using partition, it is advisable to use local index instead of global index. This is because if we use global index, we need to rebuild the index whenever we drop any partition.

Also when creating partition, you cannot create partition of an smaller range than the one that already exist.