Using partitions to manage large data volumes efficiently

Partitioning speeds up access to large data volumes and opens the door to efficient archiving strategies — transparently, as far as the accessing application is concerned.

Overview

Oracle offers a wide range of options for holding data in partitions and for speeding up both data access and the management of large data volumes. Some of the benefits are:

• Reduced I/O: when accessing partitions, only the data of the relevant index or table partition is read.
• Ideal for archiving: partitions can be moved as a whole (partition exchange). Tables partitioned by date with different intervals can be held on different storage tiers, so historical partitions can be compressed and moved to archive storage.
• Ideal for housekeeping: partitions of a table can be emptied with truncate or removed with drop. No redo is generated and the operation requires no downtime. This is the most efficient way to delete large volumes of historical data.

Example of a partitioning design

Current data is held in daily partitions, historical data in monthly partitions. This keeps the number of partitions bounded. Regular maintenance jobs can move historical partitions to tape and remove partitions with truncate or drop, which avoids resource-intensive delete statements.