11g Interval Partitioning
Oracle 11g New Features Tips by Burleson
June 27, 2008
Oracle 11g SQL New Features Tips
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:
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:
pos_data partition for (to_date('15-AUG-2007','dd-mon-yyyy'));
Another useful feature of partitioning is the
ability to distribute partitions across different tablespaces.
With interval partitioning, this can be accomplished by naming all
of the tablespaces in the table definition’s “store in” clause.
The system created partitions are then assigned to different
tablespaces in a round robin manner. For example, if the
choice was to distribute the table across three tablespaces -
tablespaceA, tablespaceB, and tablespaceC - use the following clause
in the table definition.
STORE IN (tablespaceA, tablespaceB, tablespaceC)
There are a few restrictions on interval
partitioning that must be taken into consideration before deciding
if it is appropriate for the business requirement:
Cannot be used for index organized
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
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.
If interval partitioning is a feature
that fits the requirement, there are a few new commands available 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:
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 15, 2007 is inserted into the table,
it will cause an error.
into pos_data_range (start_date, store_id, inventory_id, qty_sold)
2 values ( '15-AUG-07', 1, 1, 1);
insert into pos_data_range (start_date, store_id, inventory_id,
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
This range partitioned table can easily be
converted to use interval partitioning by using the following
pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));
Interval partitioning is now enabled, and the
row with 15-AUG-07 can be inserted without error since Oracle will
automatically create the new partition. To convert the table back to
only range partitioning, use the following command:
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.
Using the same syntax, the interval can also be
changed for existing interval partitioned tables. If changing
the original table to be partitioned every three months instead of
pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));
After inserting a row with the date of
15-NOV-07, a new partition is automatically generated with a high
value of 01-DEC-07.
pos_data (start_date, store_id, inventory_id, qty_sold)
('15-NOV-07', 1, 1, 1);
TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
The tablespace storage of the interval
partitioned table can also be changed using a similar syntax.
For example, when using a round robin tablespace assignment for the
table between tablespace1 to tablespace3, issue the following
pos_data set STORE IN(tablespace1, tablespace2, tablespace3);
Oracle interval partitioning offers a very
useful extension to range partitioning. This greatly improves
the manageability of range partitioned tables. In addition to
providing system generated new partitions, Oracle has provided a new
syntax to simplify the reference of specific partitions.
Furthermore, Oracle offers a group of commands to manage the new
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off.