Partitioning in Oracle 11g

Partitioning is one of the most sought after options for data warehousing. Almost all Oracle data warehouses use partitioning to improve the performance of queries and also to ease the day-to-day maintenance complexities. Starting with 11G, more partitioning options have been provided and these should reduce the burden of the DBA to a great extent.

This article should be helpful to DBAs and Developers who work primarily in a data warehousing environment. The new features provided by Oracle in 11G should enhance the partitioning options and provide more flexibility for partition usage and maintenance.

Table partitioning was first introduced in Oracle version 8.0 and Oracle was the first RDBMS vendor to support physical partitioning. Both SQL Server(2000) and DB2 provide only logical partitions (using the UNION ALL views) while SQL Server 2005 supports physical partitioning though not directly (implemented through partition functions). The partitioning option in Oracle was overwhelmingly received by the user community as it improved the performance, manageability and availability of applications and most importantly, DSS applications. The wide appreciation of this feature has resulted in frequent enhancements through subsequent releases.

The following table lists the partitioning features (high level) that have evolved over each version/release:

Partition Types

Let us discuss each of the above features briefly:

Range partitioning: The data is distributed based on a range of values of the partitioning key. For example, if we choose a date column as the partitioning key, the partition “JAN-2007” will contain all the rows that have the partition key values between 01-JAN-2007 and 31-JAN-2007 (assuming the range of the partition is from first of the month to the last date in the month).

Hash Partitioning: A hash algorithm is applied to the partitioning key to determine the partition for a given row. This provides I/O balancing, but cannot be used for range or inequality queries.

List Partitioning: The data distribution is defined by a list of values of the partitioning key. This is useful for discrete lists. e.g: Regions, States etc.

Composite Partitioning: A combination of 2 data distribution methods are used to create a composite partition. The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method. The following composite partitions are available:
Range-Hash, Range-List, Range-Range, List-Range, List-List and List-Hash.

Index organized tables (tables where index and data are stored together) supports partitioning by Range, List or Hash in 10G. However, composite partitions are not supported on Index organized tables.

Interval Partitioning: Introduced in 11g, interval partitions are extensions to range partitioning. These provide automation for equi-sized range partitions. Partitions are created as metadata and only the start partition is made persistent. The additional segments are allocated as the data arrives. The additional partitions and local indexes are automatically created.

      SQL>CREATE TABLE SALES_PART
       	    (TIME_ID    NUMBER,
         	    REGION_ID NUMBER,
         	    ORDER_ID NUMBER,
                 ORDER_DATE DATE,
                 SALES_QTY NUMBER(10,2),
                 SALES_AMOUNT NUMBER(12,2)
      	    )
     	   PARTITION BY RANGE (ORDER_DATE)
     	   INTERVAL (NUMTOYMINTERVAL(1,'month')
     	   (PARTITION p_first VALUES LESS THAN ('01-JAN-2006');

An Interval partitioned table can have classical range and automated interval section. Range partitioned tables can be extended to Interval partitioned tables by using the SET INTERVAL option of the ALTER TABLE command.

REF Partitioning: This partitioning scheme has been introduced with the assumption that related tables would benefit from same partitioning strategy. The detail table inherits the partitioning strategy of the master table through PK-FK relationship. There is no need for the partitioning key to be stored in the detail table and by specifying “PARTITION BY REFERENCE” keyword, the detail table inherits the partitioning strategy of the master table.

Virtual Column Based partitioning: In the previous versions of Oracle, a table could be partitioned only if the partition key physically existed in the table. The new functionality in Oracle 11G, “Virtual columns”, removes this restriction and allows partitioning key to be defined by an expression that uses one or more columns of a table. The virtual columns are stored as metadata only.
e.g.: Adding a virtual column to the table ACCOUNTS:

SQL>CREATE TABLE ACCOUNTS
(acc_no number(10) not null,
acc_name varchar2(50) not null, 
acc_loc  varchar2(5),
acc_branch number(2) generated always as
(to_number(substr(to_char(acc_no),1,2)));

Using the virtual column as the partitioning key:

SQL>CREATE TABLE accounts
(acc_no number(10) not null,
acc_name varchar2(50) not null, 
acc_loc varchar2(5),
acc_branch number(2) generated always as
(to_number(substr(to_char(acc_no),1,2)))
partition by list (acc_branch);

The Partition Advisor

Oracle 11g also provides Partition Advisor, that supports generating partitioning recommendations similar to what was provided in 10G for materialized views, materialized view logs and indexes. In fact, partition advisor is a part of the SQL Access Advisor in oracle 11g. This advisor helps to generate recommendations that will show the anticipated performance gains that will result if the partitions are implemented. It also generates the script for creating the efficient partitions which can be manually submitted to Oracle through SQL*Plus or can be queued through Enterprise Manager.