Partitioning vs. Traditional DML to Maintain a Rolling Set of
Data  Posted at Monday, June 13, 2005 The Partitioning option of
the Oracle RDBMS is heralded as important by many, but
understood - in practice - by relatively few. The general rule
of thumb is that partitioning should be employed in cases where
table and/or index segments are 2GB+ in size. Partitioning can
be used to solve a variety of problems, beyond its inherent
strength in breaking entities' contents into smaller, more
manageable pieces. The subject of this post is just such a case.
A customer has a large transaction auditing table, which is used
in Customer Support operations. There are many transactions and
the data is only useful going back 30 days in the past. Due to
the amount of data and the fact that internal customers only use
this data for an ephemeral period of time, we need to prune
unnecessary data from the table on a regular basis; in essence,
maintaining a rolling set of data, representing the last 30 days
of activity.  Normall