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