The Benefits of Partition for Improved Performance in Oracle Database 11g

Oracle Partitioning is an important option of Oracle Database 11g Enterprise edition. This adds to the efficient manageability, performance and ease of use of a wide range of applications.

Partitioning is an option that allows the database objects such as tables, indexes, index organized tables to be subdivided into smaller portions so that they can be more easily managed, controlled and accessed at a finer level of granularity.

For every requirement in business Oracle can provide an end to end appropriate partitioning scheme. Because of its transparency in SQL statements it can be applied to provide excellent benefits to any application from OLTP to DataWarehousing.

In terms of Manageability, since by the process of partitioning the database is divided into smaller units the database administrators have the flexibility to manage data by a “divide and conquer” approach.

Maintenance such as backup and recovery are simplified as they can be focused on the partitions alone. Adding a single partition or dropping a single partition is an efficient data dictionary operation that consumes less resources and time than using MODIFY or ADD command on an entire table.

In terms of Performance, when data volumes increase, oracle partitioning prevents detrimental performance because query cause searches by oracle only in the required partition instead of entire table. Some of the main performance benefits lie in Partitioning Pruning and Partition Wise Joins.

Partition pruning is the simplest method to enhance performance. Here the table is partitioned by the field used as key in query search. If a table has historical data this is extremely useful. Oracle utilizes portioning pruning inclusive with indexing technique join or parallel access method.

Partition is useful to improve the performance of multi table joins by means of a technique known as partition-wise join. This can be used on two tables being joined together with both being partitioned on the join key. It offers considerable benefits in both serial and parallel execution cases.

In terms of Availability, partitioned objects provide partition independence. Even if one part of a table is unavailable a particular time all the other partitions are online and available and the application can continue querying the table successfully so long as the unavailable part is not required to be accessed. Hence this results in decreased downtime schedule.

In terms of Information lifecycle management, through partitioning vast quantities of data can be stored at lower costs. Different information depending on its criticality can be stored on different storage tiers with different physical attributes and costs. Most recent data can be stored on an expensive high capacity storage tier and remaining can be stored on a low cost storage tier without impacting access of the end user.

With Oracle11g the new features of Partitioning introduced are System Partitioning, Interval partitioning, Virtual Column Partitioning and Reference Partitioning.

The new system partitioning feature enables new partitions to be implemented without the partition key. Instead partitions are mapped to table spaces. The main benefit of using this is for manageability purpose as the usual performance benefits do no hold good with system partitions.

Interval Partitioning enhances the concept of range partitioning. It informs Oracle to set up partitions for a specific interval automatically when data inserted into tables exceed range partitions. Its main benefit is that it eases the management of new partitions for the Database administrator.

The Virtual Column based partitioning allows data partitioning to be based on virtual column (New feature in oracle 11g based on derivation from a function or result of evaluated expressions) as the partitioning key. These have certain limitation but the plus feature is that they allow the flexibility of creation of partitions based on business requirements.

Reference Partitioning is the concept that allows tables related by foreign keys to be rationally equi-partitioned with certain restrictions applied. The child table is partitioned based on the partitioning key of parent table and the partition maintenance operations of the parent table are reflected on the child table.

Thus Oracle Partitioning is for everyone who stresses on the value of performance, manageability and availability of the database application. Its application can be equally successful both to cutting edge solutions and to commonplace database applications for simplification of administration and management of costs.

| An Introduction to Oracle’s JD Edwards EnterpriseOne Tools | Avoiding-sql-injection-in-pl-sql-oracle1og | Developing PL/SQL Web Applications in Oracle10g: An Overview | Identifying Rows by Address in Oracle 10g | Introducing Oracle OLAP option to Oracle Database 11g | Some Exciting New Features of Oracle11g | The Benefits of Partition for Improved Performance in Oracle Database 11g | Understanding Automatic SQL Tuning in Oracle10g | Using XMLTextReader Class for Better Access to XML |