Partitioned Tables and Indexes Example

This section presents an example of moving the time window in a historical table.

A historical table describes the business transactions of an enterprise over intervals of time. Historical tables can be base tables, which contain base information; for example, sales, checks, and orders. Historical tables can also be rollup tables, which contain summary information derived from the base information using operations such as GROUP BY, AVERAGE, or COUNT.

The time interval in a historical table is often a rolling window. DBAs periodically delete sets of rows that describe the oldest transactions, and in turn allocate space for sets of rows that describe the most recent transactions. For example, at the close of business on April 30, 1995, the DBA deletes the rows (and supporting index entries) that describe transactions from April 1994, and allocates space for the April 1995 transactions.

Now consider a specific example. You have a table, order, which contains 13 months of transactions: a year of historical data in addition to orders for the current month. There is one partition for each month. These monthly partitions are named order_yymm, as are the tablespaces in which they reside.

The order table contains two local indexes, order_ix_onum, which is a local, prefixed, unique index on the order number, and order_ix_supp, which is a local, non-prefixed index on the supplier number. The local index partitions are named with suffixes that match the underlying table. There is also a global unique index, order_ix_cust, for the customer name. order_ix_cust contains three partitions, one for each third of the alphabet. So on October 31, 1994, change the time window on order as follows:

1. Back up the data for the oldest time interval.

   ALTER TABLESPACE order_9310 BEGIN BACKUP;
   ...
   ALTER TABLESPACE order_9310 END BACKUP;
   

2. Drop the partition for the oldest time interval.

   ALTER TABLE order DROP PARTITION order_9310;
   

3. Add the partition to the most recent time interval.

   ALTER TABLE order ADD PARTITION order_9411;
   

4. Re-create the global index partitions.

   ALTER INDEX order_ix_cust REBUILD PARTITION order_ix_cust_AH;
   ALTER INDEX order_ix_cust REBUILD PARTITION order_ix_cust_IP;
   ALTER INDEX order_ix_cust REBUILD PARTITION order_ix_cust_QZ;
   

Ordinarily, the database acquires sufficient locks to ensure that no operation (DML, DDL, or utility) interferes with an individual DDL statement, such as ALTER TABLE ... DROP PARTITION. However, if the partition maintenance operation requires several steps, it is the database administrator's responsibility to ensure that applications (or other maintenance operations) do not interfere with the multistep operation in progress. Some methods for doing this are:

• Bring down all user-level applications during a well-defined batch window.

• Ensure that no one can access table order by revoking access privileges from a role that is used in all applications.