Oracle Partition - Range Interval Partitioning

About

A range partitioning where the database automatically creates partitions for a specified interval.

Prerequisites

  • At least one range partition using the PARTITION clause.
  • The partitioning key can only be a single column name from the table
  • The single partitioning key column must be of NUMBER or DATE type.
  • The single partitioning key column can not contains NULL (you can specify it either)
  • The table is not an index-organized tables.
  • The partitioning keys must be in the primary key if the primary key is local.

A virtual column can be used to partition on a transformed column value

The max number of partition is 1 Million, and for an integer column partition key:

<MATH> \frac{Max(integerColumn)}{Interval} < 1 M </MATH>

Syntax

See Reference

_

where:

  • partition: partition name. Example: “20110701”

Concept

Transition point

The high value of the range partitions is the transition point. The database automatically creates interval partitions for data beyond that transition point.

Example: the transition point is 1-1-2010 because 1-1-2010 is the max date.

PARTITION BY RANGE (time_id) 
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
  • The high bound of partition p3 represents the transition point. p3
    • All partitions below the transition point (p0, p1, and p2 in this example) are in the range section
    • All partitions above the transition point fall into the interval section.

Interval

The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table.

Example: the interval is one month (NUMTOYMINTERVAL(1, 'MONTH'))

PARTITION BY RANGE (time_id) 
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

See function:

Boundary

The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

Example:

  PARTITION BY RANGE (time_id) 
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
  • the lower boundary for the January 2010 interval is January 1, 2010.
  • the lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created.

Out of bound example: using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.

Storage

The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.

Management

Change the Interval

to set a new interval for the table

ALTER TABLE t_part SET INTERVAL(NUMTODSINTERVAL(1,'DAY'));

Disable / turn off interval partitioning

You can disable interval partitioning with the following statement:

ALTER TABLE <table name> SET INTERVAL ();

Example

An interval partitioned table with monthly intervals where above the transition point of January 1, 2010, partitions are created with a width of one month.

CREATE TABLE interval_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , channel_id     CHAR(1)
    , promo_id       NUMBER(6)
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
  PARTITION BY RANGE (time_id) 
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

Documentation / Reference


Powered by ComboStrap