Oracle Partitioning Features 11g

Oracle Partitioning, first introduced in Oracle 8.0 in 1997, is one of the most important and successful functionalities of the Oracle database, improving the performance, manageability, and availability for tens of thousands of applications. Partitioning is a divide-and-conquer approach to improving Oracle maintenance and SQL performance.  Anyone with un-partitioned databases over 500 gigabytes is courting disaster.  Databases become unmanageable, and serious problems occur: 

• Files recovery takes days, not minutes
• Rebuilding indexes (important to re-claim space and improve performance) can take days
• Queries with full-table scans take hours to complete
• Index range scans become inefficient

Partitioning

·         enhances the performance, manageability, and availability of a wide variety of applications

·         Helps reduce the total cost of ownership for storing large amounts of data.

·         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.

Oracle Database 11g introduces the 8^thgeneration of partitioning which continues to offer ground-breaking new and enhanced functionality and new partitioning techniques enabling customers to model even more business scenarios.

Partitioning Extensions:

In addition to the basic partitioning strategies, Oracle provides partitioning extensions. The extensions in Oracle Database 11g mainly focus on two objectives:

(a) Enhance the manageability of a partitioned table significantly.

(b) Extend the flexibility in defining a partitioning key.

Describing here the Oracle 11g Features found very useful during development phase of the Project:

1.      Interval Partitioning

2.      Exchange Partition

3.      Compress Partition

.

1.    Interval Partitioning

Interval Partitioning: A new partitioning strategy in Oracle Database 11g, Interval Partitioning extends the capabilities of the range method to define equipartitioned ranges using an interval definition. Rather than specifying individual ranges explicitly, Oracle will create any partition automatically as-needed whenever data for a partition is inserted for the very first time. Interval Partition greatly improves the manageability of a partitioned table.

Range partitioning allows table to be partitioned by a specified range on the partitioning key.  For example, if a table was used to store sales data, it might be range partitioned by a DATE column, with data for each month in a different partition. 

Therefore every month a new partition would need to be defined in order to store rows for that month.  If a row was inserted for a new month before a partition was defined for that month, the following error would result and data loading will fail until the new partitions are created:

ORA-14400: inserted partition key does not map to any partition 

Ø  Interval Partitioning Commands

There are a few new commands to manage interval partitioning.  First, convert a range partitioned table to use interval partitioning by using

alter table <table_name> set interval (expr).

Consider this range partitioned table:

Create table
pos_data_range (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER (6),
   qty_sold          NUMBER (3)
)
   PARTITION BY RANGE (start_date)
( 
   PARTITION pos_data_p0 VALUES LESS THAN (TO_DATE ('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE ('1-8-2007', 'DD-MM-YYYY'))
); 

If a row with a date of August 31, 2007 is inserted into the table, it will cause an error.

insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
    values ( '31-AUG-07', 1, 1, 1);
insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

·         Range Partitioned to Interval Partitioned

Alter table pos_data_range set INTERVAL (NUMTOYMINTERVAL (1, 'MONTH'));

Interval partitioning is now enabled, and the row with 31-AUG-07 can be inserted without error since Oracle will automatically create the new partition.

·         Interval Partitioned to Range Partitioned

To convert the table back to only range partitioning, use the following command:

Alter table pos_data_range set INTERVAL();

The table is converted back to a range partitioned table and the boundaries for the interval partitions are set to the boundaries for the range partitions.

Ø  An Interval Partitioning Example

Interval partitioning can simplify the manageability by automatically creating the new partitions as needed by the data.  Interval partitioning is enabled in the table’s definition by defining one or more range partitions and including a specified interval.  For example, consider the following table:

Create table
pos_data (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER (6),
   qty_sold          NUMBER (3),
)
PARTITION BY RANGE (start_date)
INTERVAL (NUMTOYMINTERVAL (1, 'MONTH'))
( 
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE ('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE ('1-8-2007', 'DD-MM-YYYY'))
); 

Here, two partitions have been defined and an interval of one month has been specified.  If data is loaded into this table with a later date than the greatest defined partition, Oracle will automatically create a new partition for the new month.  In the table above, the greatest defined interval is between July 1, 2007 and August 1, 2007. 

Inserting a row that has a date later than or equal to August 1, 2007 would raise an error with Normal range partitioning unless you create a partition for the new period and insert rows for that period into the table.  However, with interval partitioning, Oracle determines the high value of the defined range partitions, called the transition point, and creates new partitions for data that is beyond that high value.

Insert into pos_data (start_date, store_id, inventory_id, qty_sold) values ('31-AUG-07, 1, 1, 1);

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME='POS_DATA'  

PARTITION_NAME	HIGH_VALUE
POS_DATA_P0	TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
POS_DATA_P1	TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P81	TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Notice that a system generated partition named SYS_P81 has been created upon inserting a row with a partition key greater than the transition point.  Oracle will manage the creation of new partitions for any Partition Key value.  Therefore, the values do not need to be inserted in sequence.

Since the partitions are named automatically, Oracle has added a new syntax in order to reference specific partitions effectively.  The normal way to reference a specific partition is to use the partition (partition_name) in the query:

Select * from pos_data partition (SYS_P81);

However, it would be cumbersome to look up the system generated partition name each time.  Therefore, the new syntax to specify a partition is by using the partition for (DATE) clause in the query:

Select * from pos_data partition for (to_date ('31-AUG-2007','dd-mon-yyyy')); 

Ø  Restrictions on Interval Partitioning

• Cannot be used for index organized tables
• Must use only one partitioning key column and it must be a DATE or NUMBER
• Cannot create domain indexes on interval partitioned tables
• Are not supported at the sub-partition level

·         This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable. If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

2.    Exchanging Partitions and Sub partitions with Tables

One of the great features about partitioning, and most specifically range-based partitioning, is the ability to load data quickly and easily with minimal impact on the current users using ALTER TABLE ... EXCHANGE PARTITION statement—that is, to move any existing rows in the partition or sub partition to the no partitioned table, and any existing rows in the no partitioned table to the table partition or sub partition.

Ø  Syntax of the ALTER TABLE ... EXCHANGE PARTITION statement

ALTER TABLE pt            

    EXCHANGE PARTITION p

    WITH TABLE t

INCLUDING INDEXES
 WITHOUT VALIDATION;

Where pt is the partitioned table, p is the partition or subpartition to be exchanged, and t is the nonpartitioned table to be exchanged with p.

This command swaps over the definitions of the named partition and the table t, so that the data suddenly exists in the right place in the partitioned table. Moreover, with the inclusion of the two optional extra clauses, index definitions will be swapped and Oracle will not check whether the data actually belongs in the partition - so the exchange is very quick.

Ø  An Exchange Partition Example

Alter table call exchange partition data_2007 with table call_temp;

Below is an example where it is observed that while moving 180 MB of data:

(1) Create as select method it takes around 15 seconds
(2) Partition exchange method is taking only three seconds.

The partition exchange method also takes care of the index on the source table. So efficiency of the partition exchange is more than five times.

Test Instance
Schema: XYZ_A01 (with DBA privilege)
Source Table : CALL_TEMP
Destination Table : CALL (with partition)

--Start with a clean schema

DROP TABLE call;
DROP TABLE call_temp;

Select owner||’.’||segment_name OBJECT_NAME, round (bytes/1024/1024,1) MB from dba_segments

where owner = ‘XYZ_A01’ and

segment_name like ‘%CALL%’;

--no rows selected

Start time keeping

Set timing on

--Create the temporary (source) Table which is used to load the data offline

DROP TABLE call_temp;
CREATE TABLE call_temp (
id NUMBER(12,6),
v1 VARCHAR2(10),
data VARCHAR2(100)
);

--Insert Records into the offline (source) table

Load 1'000'000 Rows into the offline (source) table
INSERT /*+ append ordered full(s1) use_nl(s2) */
INTO call_temp
SELECT
TRUNC((ROWNUM-1)/500,6),
TO_CHAR(ROWNUM),
RPAD('X',100,'X')
FROM
all_tables s1,
all_tables s2
WHERE
ROWNUM <= 1000000;
commit;

Elapsed: 00:05:44.09

--Check size of the Temp table

select owner||’.’||segment_name OBJECT_NAME, round (bytes/1024/1024,1) MB from dba_segments

where owner = ‘XYZ_A01’ and

segment_name like ‘%CALL%’;

--------------------------------\
OBJECT_NAME          MB
-------------------- ----------
XYZ_A01.CALL_TEMP 189.8
--------------------------------/

--Add Primary Key to the offline Table

--ALTER TABLE call_temp
--ADD CONSTRAINT pk_call_temp PRIMARY KEY (id)
--USING INDEX (CREATE INDEX pk_call_temp ON call_temp (id) NOLOGGING);

CREATE INDEX pk_call_temp ON call_temp (id) NOLOGGING;
--(Elapsed: 00:00:06.09)

ALTER TABLE call_temp
ADD CONSTRAINT pk_call_temp PRIMARY KEY (id);
--(Elapsed: 00:00:07.04)

----Check size of the Temp table and the Index

Select owner||’.’||segment_name OBJECT_NAME, round (bytes/1024/1024,1) MB from dba_segments where owner = ‘XYZ_A01’ and segment_name like ‘%CALL%’;

---------------------------------------\
OBJECT_NAME           MB
-------------------- ----------
XYZ_A01.CALL_TEMP    189.8
XYZ_A01.PK_CALL_TEMP 25
-------------------------------/

---Case I: Create destination table directly

drop table call;
create table call as select * from call_temp;

Elapsed: 00:00:14.03 --I

---Case II: Create the partition (destination) table

DROP TABLE call;
CREATE TABLE call (
id NUMBER(12,6),
v1 VARCHAR2(10),
data VARCHAR2(100)
)
PARTITION BY RANGE(id) ( -- Partion Key = Primary Key
PARTITION call_partition VALUES LESS THAN (MAXVALUE)
);

Select owner||’.’||segment_name OBJECT_NAME, round (bytes/1024/1024, 1) MB from dba_segments where owner = ‘XYZ_A01’ and segment_name like ‘%CALL%’;

------------------------------------------\
OBJECT_NAME          MB
-------------------- ----------
XYZ_A01.CALL_TEMP 189.8
XYZ_A01.CALL 0
XYZ_A01.PK_CALL_TEMP 25
------------------------------------------/

--Add local primary key to the partition table as a local index (destination)

--ALTER TABLE call
--ADD CONSTRAINT pk_call PRIMARY KEY(id)
--USING INDEX (CREATE INDEX pk_call ON CALL(id) NOLOGGING LOCAL);

CREATE INDEX pk_call ON CALL(id) NOLOGGING LOCAL;
ALTER TABLE call
ADD CONSTRAINT pk_call PRIMARY KEY(id);

--------------------------------\
OBJECT_NAME          MB
-------------------- ----------
XYZ_A01.CALL_TEMP 189.8
XYZ_A01.CALL 0
XYZ_A01.PK_CALL_TEMP 25
XYZ_A01.PK_CALL 0
--------------------------------/

--Now swap the offline Table into the partition (source into destination)
=====================================================================
ALTER TABLE CALL
EXCHANGE PARTITION call_partition WITH TABLE call_temp
INCLUDING INDEXES
WITHOUT VALIDATION;

Elapsed: 00:00:02.06 –II

--Check size of the destination table after Exchange

select owner||’.’||segment_name OBJECT_NAME, round (bytes/1024/1024,1) MB from dba_segments where owner = ‘XYZ_A01’ and segment_name like ‘%CALL%’;

----------------------------------------\
OBJECT_NAME          MB
-------------------- ----------
XYZ_A01’.CALL_TEMP 0
XYZ_A01’.CALL 189.8
XYZ_A01’.PK_CALL_TEMP 0
XYZ_A01’.PK_CALL 25
----------------------------------------/

So data from main table as well as index moved to the destination.

Conclusion: The exchange partition method is 14.03/02.06 = 6.8 times faster than normal insert method.

Ø  Restrictions

The table to be exchanged must not be partitioned, but must otherwise have the same table structure as the partitioned table.

·         The table to be exchanged must not be a temporary table.

·         Any rows existing in the nonpartitioned table prior to the exchange must lie within the range defined for the partition or subpartition.

·         The table to be exchanged may not have any foreign keys, nor may any other tables have foreign keys which reference this table. The table to be exchanged may not have any foreign keys, nor may any other tables have foreign keys which reference this table.

·         In addition to the ALTER, INSERT, and CREATE privileges usually required for ALTER TABLE statements, you must have the DROP privilege to perform ALTER TABLE ... EXCHANGE PARTITION.

·         Executing this statement does not invoke any triggers on either the partitioned table or the exchanged table.

·         Any AUTO_INCREMENT columns in the exchanged table are reset.

3.    Partition compression

As we know that in 10g we were able to compress on whole table only but in 11g we can use compress on partition level.

Ø  Syntax:

ALTER TABLE... COMPRESS;

ALTER TABLE... NOCOMPRESS;

Ø  Compressing Table at Partition Level.

Example:

CREATE TABLE test

           (sno       NUMBER(6),

            last_name VARCHAR2 (30),

            salary    NUMBER (6))

          PARTITION BY RANGE (salary)

           INTERVAL (5000)

          (

     PARTITION p1 VALUES LESS THAN (5000)   COMPRESS,

     PARTITION p2 VALUES LESS THAN (10000) NOCOMPRESS,

     PARTITION p3 VALUES LESS THAN (15000) COMPRESS,

     PARTITION p4 VALUES LESS THAN (20000)) NOCOMPRESS;

SELECT table_name, partition_name, compression

   FROM user_tab_partitions

   WHERE table_name='TEST';

TABLE_NAME      PARTITION_NAME              COMPRESS

--------------- --------------------------- --------------

TEST            P1                          ENABLED

TEST            P2                          DISABLED

TEST            P3                          ENABLED

TEST            P4                          DISABLED

·         We can define compression for whole partition by using single keyword ‘COMPRESS’ like this.

CREATE TABLE test

               (sno       NUMBER (6),

                last_name VARCHAR2 (30),

                salary    NUMBER (6))

           COMPRESS

              PARTITION BY RANGE (salary)

               INTERVAL (5000)

              (

         PARTITION p1 VALUES LESS THAN (5000),

        PARTITION p2 VALUES LESS THAN (10000),

        PARTITION p3 VALUES LESS THAN (15000),

        PARTITION p4 VALUES LESS THAN (20000));

Table created.

SELECT table_name, partition_name, compression

   FROM user_tab_partitions

   WHERE table_name='TEST';

TABLE_NAME                     PARTITION_NAME                COMPRESS

------------------------------ ----------------------------- -------------

TEST                           P1                            ENABLED

TEST                           P2                            ENABLED

TEST                           P3                            ENABLED

TEST                           P4                            ENABLED

Ø  Compressing Individual Partitions in the warehouse

Let us consider the following example to illustrate how the Compress Partitioning feature saves a large amount of costly space on disk.

-- Check size of Partition before compress

select segment_name, sum(bytes)/(1024*1024*1024) table_size_meg

from user_extents where segment_type='TABLE PARTITION'

and   segment_name = ‘VAL_TEMP’     

AND PARTITION_NAME ='VAL_TEMP_VCHR_201309'

group by segment_name;

segment_name                      table_size_meg                                                           
----------------                  --------------                                                          
VAL_TEMP                          14.3974609375

--Compress Table for Partition VAL_TEMP_VCHR_201309

group by segment_name;

ALTER TABLE VAL_TEMP

MOVE partition VAL_TEMP_VCHR_201309

COMPRESS FOR ALL OPERATIONS

NOLOGGING;

--Check size of Partition after compress

select segment_name,sum(bytes)/(1024*1024*1024) table_size_meg

from user_extents where segment_type='TABLE PARTITION' and

segment_name = 'VAL_TEMP’

AND PARTITION_NAME ='VAL_TEMP_VCHR_201309'

group by segment_name;

segment_name                       table_size_meg                                                              
----------------                   --------------                                                      
VAL_TEMP                           1.1552734375

That gives us a compression ratio of 12.5 which is quite nice if our goal is to reduce costly space on disk.

Ø  COMPRESS FOR ALL OPERATIONS

select compression, compress_for
from user_tab_partitions
where partition_name = 'SALES_1995';

You get something like this:

COMPRESS       COMPRESS_FOR
-----------    --------------------
ENABLED DIRECT LOAD ONLY

Compress table at Partition level

ALTER TABLE sales
MOVE PARTITION sales_1995
COMPRESS FOR ALL OPERATIONS
NOLOGGING;

And ask again, what we compressed for you get the expected result:

COMPRESS       COMPRESS_FOR
-------------- ------------------------
ENABLED        FOR ALL OPERATIONS

What this means is that we now have compression on, and if we insert a row, or update a row, we still keep the compression going. The earlier status is really the one to use for older data that you do not see changing at all. COMPRESS FOR ALL OPERATIONS is actually the 11g Advanced Compression database priced option.