Donald K. Burleson Advanced Oracle Tips and Tricks for the Oracle DBA

Oracle 11g Partitioning New Features

Oracle 11g introduces many new features and enhancements for database patronizing. Among the new types and methods for data partitioning include the following:

• System Partitioning

• Interval Partitioning

• Virtual Column Partitioning

• Reference Partitioning

System Partitioning New Feature in Oracle 11g

The new system partitioning feature in Oracle 11g provides you with the ability to implement and manage new partitions without a specific partition key. Instead, each partition is mapped to a tablespace using the extended partitioning syntax for system partitions. Due to lack of partition keys with system partitioning, the usual performance benefits available for partitioned tables do not exist with system partitions. Another drawback to system partitions is that they cannot be used for partition-wise joins or traditional partition pruning operations. As such, the main benefit of using the new system partitioning is for manageability purposes. An example of how the system partition feature is shown in the following example figure.

First, let’s create a test table for usage with system partitioning.

SQL> CREATE TABLE system_part_table (a1 integer, a2 integer)
  2  PARTITION BY SYSTEM
  3  (
  4  PARTITION p1 TABLESPACE USERS,
  5  PARTITION p2 TABLESPACE EXAMPLE
  6  );

Table created.

The above new table was created with two system partitions assigned to USERS and EXAMPLE tablespaces. Of special note is the syntactical element SYSTEM is used to denote a system based partitioned table. Now let’s insert some test data into our new system partitions.

SQL> INSERT INTO system_part_table PARTITION (p1) VALUES (1,2);
1 row created.

SQL> INSERT INTO system_part_table PARTITION (p2) VALUES (3,4);
1 row created.

 We can verify the status for our new system partitions with the following query against the    USER_TAB_PARTITIONS view:

SQL> select table_name, partition_name, tablespace_name
  2  from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
SYSTEM_PART_TABLE              P1
USERS

SYSTEM_PART_TABLE              P2
EXAMPLE

 While system partitioning is useful in cases when other partitioning strategies are not possible, there are  limitations to its usage.

 For example, the following operations are not supported for system partitioning:

• CREATE TABLE AS SELECT

• INSERT INTO <table_name> AS <sub query>

• SPLIT PARTITION operations

• Unique Local Indexes are not supported as these require a partition key

 As a workaround to these limitations, you should first create the table with the system partitions and   then insert rows for each partition.

 Interval Partitioning for Oracle 11g

 Interval partitions build upon the foundation introduced with range partitioning for Oracle 11g. Interval   partitioning resolves the limitations built into range partitioning when a specific range is unknown by the  developer or DBA creating the partitions for the table. It tells Oracle to automatically setup new  partitions for a particular interval when data inserted to tables are greater than the range partitions. As  such the requirement of interval partitioning dictates that at least one range partition is specified. Once  the range partitioning key is given for the high value of the range partitions, this transition point is used  as the baseline to create interval partitions beyond this point. The nice thing about the new interval  partitioning feature for Oracle 11g is that it eases the management of new partitions for the busy  Oracle DBA or development staff. The following exercise will demonstrate how interval partitioning  works with Oracle 11g.

 First we will create a new table to use for interval partitioning:

SQL> CREATE TABLE sales_interval
  2  (product_id                NUMBER(6),
  3  customer_id                NUMBER,
  4  time_id                    DATE,
  5  channel_info               CHAR(1),
  6  promo_id                   NUMBER(6),
  7  qty_sold                   NUMBER(3),
  8  amt_sold                   NUMBER(10,2)
  9  )
 10  PARTITION BY RANGE (time_id)
 11  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
 12  (PARTITION t0 VALUES LESS THAN (TO_DATE('1-1-2005','DD-MM-YYYY')),
 13  PARTITION t1 VALUES LESS THAN (TO_DATE('1-1-2006','DD-MM-YYYY')),
 14  PARTITION t2 VALUES LESS THAN (TO_DATE('1-7-2006','DD-MM-YYYY')),
 15  PARTITION t3 VALUES LESS THAN (TO_DATE('1-1-2007','DD-MM-YYYY')) ); 

Table created.

 The above statement creates a table with four new interval based partitions using a one month period   for the width of the interval with January 1, 2007 as the transition point with t3 partition as the high  bound and the other partitions (t0-t2) as the range section with all partitions above it occur in the  interval range. However, like other forms of partitioning there are some limitations as follows.

 Interval partitioning restrictions include:

• Index Organized tables (IOTs) are not supported by interval partitioning.

• Domain index cannot be created on interval partitioned tables.

• Only one partitioning key column can be set for the interval partition and it must be either a DATE or NUMBER data type.

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.