Deleting large numbers of rows quickly

[tennis]

We do some fairly heavy transaction processing. We have some important tables we desire to keep as small as possible by deleting out transactions that are successfully processed. We have a batch process that runs against the table every day and marks transactions as processed by changing a status. We then can delete these rows as time permits. It can take a long time.

Are 'table-controlled partitions' using a status column (perhaps 10 possible values in the domain) appropriate? I see a fair amount on-line of how to create these partitions, but not a lot on why or when to use them. They seem to be just the thing - partition based upon the status column, and every so often drop the partition having the 'processed' status and create a new one.

We're using UDB on an AIX box.

[Kalicharan]

Hi,

Partitioning strategy is good for that case.

In DB2, we can truncate partition alone. No need to drop the partition and add it again.
alter table table_name truncate partition partition_name;

Look whether this can be done in UDB as well.

There is no disadvantages because of partitioning apart of a little bit overhead on updates
which is negligible (as per my knowledge).
In this particular case, I find no disadvantages to go with partitioning approach.
But all the indexes on that table should be global indexes only.
If you create local indexes on those tables, there can be performance degradation.

My views are based on DB2. But as per my knowledge implementation should be same in UDB as well.

Thanks
Kalicharan