Interval partitioning

Interval partitioning is a partitioning method introduced in Oracle 11g. This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges.

The following restrictions apply:

·         You can only specify one partitioning key column, and it must be of NUMBER or DATE type.

·         Interval partitioning is NOT supported for index-organized tables.

·         You can NOT create a domain index on an interval-partitioned table.

·         Partition names are generated automatically with they Syntax “SYS_xxxx”

Interval partitioning based on a numeric value needs a correct interval definition. For e.g. the known “monthcode” value, a create table statement would look like:

CREATE TABLE autopartition

(

   monthcode        NUMBER(6)

,  employee          VARCHAR2 (40)

,  salary             NUMBER (10)

)

PARTITION BY RANGE

   (monthcode)

   INTERVAL ( 1)

   (

      PARTITION p_first

         VALUES LESS THAN (201202));

In the above table, only the first partition is named. If we insert data beyond the partition definition, a new partition is created.
Because the partitions are named automatically, Oracle has added new syntax to reference the specific partitions effectively by using either the generated name:

select

   *

from

   autopartitionnum partition (SYS_P62);

 or the range of a partition:

select

   *

from

   autopartitionnum partition for (201201);

Note that, because the partitions are names automatically, with an incremental numbering, partition SYS_P62 can belong to table A, whereas partition SYS_P63 van belong to table B, etc..etc..

Interval partitioning for DATE columns can be used based on year or month bases, using the NUMTOYM function in the INTERVAL table definition.

For interval partitioning on a more distinctive level, like days, a function called NUMTODSINTERVAL is available.
Creating an Interval partitioned table –seperated on month– then looks like:
Creating an Interval partitioned table –seperated on month– then looks like:

CREATE TABLE autopartitionmonth

(

   saldate        DATE

,  employee       VARCHAR2 (40)

,  salary         NUMBER (10)

)

PARTITION BY RANGE

   (saldate)

   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

   (

      PARTITION p_first

         VALUES LESS THAN (TO_DATE ('01-01-2012', 'DD-MM-YYYY')));

Or, with a separation of 1 day:

CREATE TABLE autopartitionday

(

   saldate        DATE

,  employee       VARCHAR2 (40)

,  salary         NUMBER (10)

)

PARTITION BY RANGE

   (saldate)

   INTERVAL ( NUMTODSINTERVAL (1, 'DAY') )

   (

      PARTITION p_first

         VALUES LESS THAN (TO_DATE ('01-01-2012', 'DD-MM-YYYY')));

Conclusions:

Interval partitioning can be used to automatically add new partitions when data is inserted into a partitioned table that is out of range of the available partitions.
We can easily switch to normal manual partitioning by omitting the “INTERVAL ( NUMTODSINTERVAL (1, 'DAY'))” line in the create statements

Advantages:

·         No more worries about partitions to be created automatically, oracle takes care of it

·         A partition “DEFAULT” for all values outside existing partitions is no longer necessary. This partition in old releases often worked like a trashcan.

Disadvantage

·         Partition naming is automatic, this means subsequently created partition names, can be part of different tables.

Index partitioning

When using Indexes on partitioned tables, make sure to use LOCAL indexes as much as possible. This means that the Index is also setup as a partitioned Index, with manageable parts on each table partition.
Where in the MISPRD all indexes where dropped before a certain table was loaded, in Oracle 11g it is now possible to set a certain Index or part of an index to “UNUSABLE”. This means the Index will not be updated during table changes, and will also not be used during selects from that table. As soon as the table changes are completed, the disabled Index or index parts cat simply rebuilt.

An example for the “autopartition” table above.

Let’s assume we want to insert/update 1000’s of records of the current month (201205) in this table.

Since we know the current month, we can use a statement like mentioned above to disable the Index for that particular partition:

ALTER TABLE autopartition MODIFY PARTITION FOR (201205) UNUSABLE LOCAL INDEXES; 

This sets the Index for that partition to UNUSABLE. This is confirmed by selecting the partition name with the high_value from the dba_ind_partitions view, like:

SELECT index_name, partition_name, high_value

  FROM dba_ind_partitions

 WHERE status = 'UNUSABLE;

The result looks like:

INDEX_NAME	PARTITION_NAME	HIGH_VALUE
AUTOPARTITION_X1	SYS_P69	201206

As soon as the load action is completed, all indexes partitions that are set to unusable can be found using the following query:

SELECT 'alter index '||index_name||' rebuild partition '||partition_name||';'

  FROM dba_ind_partitions

 WHERE status = 'UNUSABLE';'