Partitioning

PARTITIONING

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Here are some suggestions for when to partition a table:

• Tables greater than 2 GB should always be considered as candidates for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. 
• When the contents of a table need to be distributed across different types of storage devices.

Here are some suggestions for when to consider partitioning an index:

• Avoid rebuilding the entire index when data is removed.
• Perform maintenance on parts of the data without invalidating the entire index. 
• Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

PARTITIONING FOR PERFORMANCE


By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. These features include:

• Partition Pruning
• Partition-Wise Joins

Oracle partition pruning only accesses those data blocks required by the query.Partitioning Pruning refers to the act of eliminating partitions which are not of concern and scanning only the partitions which are of concern and containing the data which is requested thereby eliminating the need to scan the entire table to search for the requested data and scanning only the partitions on which the data falls.Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.

Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined together and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table. Partition-wise joins break a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy.Storing different partitions in different tablespaces allows the database administrator to do backup and recovery operations on each individual partition, independent of the other partitions in the table.