Skip to content

11gR2 – new interval partitioning feature





Recently we were thinking on loading huge amounts of data with varying dates from the last 10 years. This table is partitioned on the date column and currently we have monthly partitions from the last 2 years. we were wondering if there was an easy way instead of creating manually all partitions from the last 10 years.

luckily we were on 11gr2 and came across this nice feature – interval partitioning.. read on on what oracle documentation has to say about this.

you can convert your existing range partitioning tables to interval partitioning.

Creating Interval-Partitioned Tables

The INTERVAL clause of the CREATE TABLE statement establishes interval

partitioning for the table. You must specify at least one range partition using the

PARTITION clause. The range partitioning key value determines the high value of the

range partitions, which is called the transition point, and the database automatically

creates interval partitions for data beyond that transition point. The lower boundary of

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

interval partition.

For example, if you create an interval partitioned table with monthly intervals and the

transition point is at January 1, 2010, then 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. Note,

however, that 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.

For interval partitioning, the partitioning key can only be a single column name from

the table and it must be of NUMBER or DATE type. 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.

The following example specifies four partitions with varying interval widths. It also

specifies that above the transition point of January 1, 2010, partitions are created with

an interval width of one month.

Example 4–4 Creating an interval-partitioned table

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-2008’, ‘DD-MM-YYYY’)),

PARTITION p1 VALUES LESS THAN (TO_DATE(‘1-1-2009’, ‘DD-MM-YYYY’)),

PARTITION p2 VALUES LESS THAN (TO_DATE(‘1-7-2009’, ‘DD-MM-YYYY’)),

Note: If your enterprise has databases using different character sets,

use caution when partitioning on character columns, because the sort

sequence of characters is not identical in all character sets. For more

information, see Oracle Database Globalization Support Guide.

Creating Partitions

Partition Administration 4-5

PARTITION p3 VALUES LESS THAN (TO_DATE(‘1-1-2010’, ‘DD-MM-YYYY’)) );

The high bound of partition p3 represents the transition point. p3 and all partitions

below it (p0, p1, and p2 in this example) are in the range section while all partitions

above it fall into the interval section.

alter_interval_partitioning

Use this clause:

■ To convert an existing range-partitioned table to interval partitioning. The

database automatically creates partitions of the specified numeric range or

datetime interval as needed for data beyond the highest value allowed for the last

range partition.

■ To change the interval of an existing interval-partitioned table. The database

converts existing interval partitions to range partitions, and then automatically

creates partitions of the specified numeric range or datetime interval as needed for

data beyond the highest value allowed for the last range partition.

■ To change the tablespace storage for an existing interval-partitioned table.

■ To change an interval-partitioned table back to a range-partitioned table. Use SET

INTERVAL () to disable interval partitioning. The database converts existing

interval partitions to range partitions, using the higher boundaries of created

interval partitions as upper boundaries for the range partitions to be created.

INTERVAL Clause
Use this clause to establish interval partitioning for the table. Interval partitions are
partitions based on a numeric range or datetime interval. They extend range
partitioning by instructing the database to create partitions of the specified range or
interval automatically when data inserted into the table exceeds all of the range
partitions.
■ For expr, specify a valid number or interval expression.
■ The optional STORE IN clause lets you specify one or more tablespaces into which
the database will store interval partition data.
■ You must also specify at least one range partition using the PARTITION clause of
range_partitions. The range partition key value determines the high value of
the range partitions, which is called the transition point, and the database creates
interval partitions for data beyond that transition point.
Restrictions on Interval Partitioning The INTERVAL clause is subject to the following
restrictions:
■ You can specify only one partitioning key column, and it must be of NUMBER or
DATE type.
■ This clause is not supported for index-organized tables.
■ You cannot create a domain index on an interval-partitioned table.
■ Interval partitioning is not supported at the subpartition level.
■ Serializable transactions do not work with interval partitioning. Trying to insert
data into a partition of an interval partitioned table that does not yet have a
segment causes an error.
■ In the VALUES clause:
– You cannot specify MAXVALUE (an infinite upper bound), because doing so
would defeat the purpose of the automatic addition of partitions as needed.
– You cannot specify NULL values for the partitioning key column.

example

i modified my table definition

11gR2 has this brilliant interval partition method. 

So removed the pmax partitions from the table and executed the interval comamnds

alter table inst_fact_ncbex_debt set interval(NUMTOYMINTERVAL(1, ‘MONTH’)); 

alter table inst_fact_ncbex_equity set interval(NUMTOYMINTERVAL(1, ‘MONTH’)); 

Currently we have partitions until october 2010 

I inserted a record having 01-11-2010 and immediately the november partition is created by oracle automatically..

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
11gR2 - new interval partitioning feature, 10.0 out of 10 based on 1 rating

Post a Comment

You must be logged in to post a comment.