Table Partitioning in Oracle

In this section I’ll be discussing about the some types of partitioning options available in Oracle , its advantages and also provide some examples.

Why Partitioning?

Oracle partitioning is mainly used for manageability, availability and performance of oracle tables. Partitioning allows tables, indexes, materialized views and Index-organized tables to be further divided in to smaller manageable pieces. Partitioning enables the database objects to be managed and accessed at a finer level of granularity.

Partitioning for manageability

The partitioning option allows indexes and tables to be partitioned in to smaller manageable units. Using partition tables, DBA’s can perform maintenance on certain partitions while the rest of the partitions are still accessed by the applications.

A typical usage of partitioning for manageability is tos upport a “rolling window” load process in a data warehouse. Imagine you have to load a table with data on a monthly basis. You can take advantage of the range partition option so that each partition contains a months worth of data.
If you have to purge 6 month old data from a table on a monthly basis. The range partitioning offers a better solution. You can just delete a partition rather than issuing a DELETE command which creates additional load on the database.

Partitioning for Performance

By limiting the amount of data to be examined or operated on and by enabling parallel execution, the Oracle Partitioning option provides a number of performance benefits.

Partition Pruning
Partition pruning is the simplest and also the most substantial means to improve performance. Partition pruning can often improve query performance by several orders of magnitude.
Imagine a Orders table containing historical records of orders and the table data is partitioned by week. A query requesting data for a single week would only access a single partition of the orders table, thus by improving the performance by a bigger magnitude. 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.

Partition Wise Joins

Partitioning can also improve the performance of multi-table joins, by using a technique known as partition-wise join. Partition-wise join can be applied with two tables being joined together and both of these tables are partitioned on the join key. Partition-wise joins breaks a large join in to 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.

Parallel Execution

Partitioning enables parallel execution of UPDATE, DELETE and MERGE statements. Oracle will parallelize SELECT statements and INSERT statements when accessing both partitioned and non-partitioned database objects. UPDATE, DELETE and MERGE statements can be parallelized for both partitioned and non-partitioned database objects when no bit map indexes are present. In order to parallelize the operations on objects having bit map indexes , the target table must be partitioned. Parallel execution of sql statements can vastly improve performance, particularly for UPADTE, DELETE or MERGE operations involving large volumes data.

Partitioning for Availability

The DBA can store different partitions in different tablespaces which would allow him/her to perform backup/recovery operations on each individual partition, independent of the other partitions in the table.

Partitioned database objects provide partition independence. If any one of the partitions become unavailable, all other partitions of the table remain online and available. Applications can still use the available partitions while the DBA can work on fixing the failed partition/partitions.


Types of Partitioning

· Range Partitioning
· Hash Partitioning
· List Partitioning
· Composite partitioning

In the next post, I’ll be discussing the above-mentioned partitioning options in detail.