Interval partitioning is an enhancement to range
partitioning in Oracle 11g and interval partitioning
automatically creates time-based
partitions as new data is added.
Range partitioning allows an object to be
partitioned by a specified range on the partitioning key. For
example, if a table was used to store sales data, it might be range
partitioned by a DATE column, with each month in a different partition.
Therefore, every month a new partition would need to
be defined in order to store rows for that month. If a row was
inserted for a new month before a partition was defined for that month,
the following error would result:
ORA-14400:
inserted partition key does not map to any partition
If this situation occurs, data loading will fail
until the new partitions are created. This can cause serious
problems in larger data warehouses where complex reporting has many
steps and dependencies in a batch process. Mission critical
reports might be delayed or incorrect due to this problem.
An Interval Partitioning Example
Interval partitioning can simplify the
manageability by automatically creating the new partitions as needed
by the data. Interval partitioning is enabled in the table's
definition by defining one or more range partitions and including a
specified interval. For example, consider the following table:
create table
pos_data (
start_date
DATE,
store_id
NUMBER,
inventory_id
NUMBER(6),
qty_sold
NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1,
'MONTH'))
(
PARTITION pos_data_p2 VALUES LESS
THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
PARTITION
pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);
Here, two partitions have been defined and an
interval of one month has been specified. If data is loaded into
this table with a later date than the greatest defined partition, Oracle
will automatically create a new partition for the new month. In
the table above, the greatest defined interval is between July 1, 2015
and August 1, 2015.
Inserting a row that has a date later than
August 1, 2015 would raise an error with normal range partitioning.
However, with interval partitioning, Oracle determines the high value of
the defined range partitions, called the transition point, and creates
new partitions for data that is beyond that high value.
insert into
pos_data (start_date, store_id, inventory_id, qty_sold)
values (
'15-AUG-07', 1, 1, 1);
SELECT
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
HIGH_VALUE
FROM
Remote DBA_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
PARTITION_NAME HIGH_VALUE
POS_DATA_P0 TO_DATE(' 2015-07-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
POS_DATA_P1 TO_DATE(' 2015-08-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P81
TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Notice that a system generated partition named
SYS_P81 has been created upon inserting a row with a partition key
greater than the transition point. Oracle will manage the creation
of new partitions for any value beyond the high value. Therefore,
the values do not need to be inserted in sequence.
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:
select
*
from
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:
select
*
from
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.
INTERVAL(NUMTOYMINTERVAL(1,
'MONTH'))
STORE IN (tablespaceA, tablespaceB, tablespaceC)
Restrictions on Interval Partitioning
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 tables
-
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
level
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.
Interval Partitioning Commands
There are a few new commands 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:
create table
pos_data_range (
start_date
DATE,
store_id
NUMBER,
inventory_id
NUMBER(6),
qty_sold
NUMBER(3)
)
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, 2015 is inserted into the table, it will cause an error.
SQL> insert
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, qty_sold)
*
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 command:
alter table
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:
alter table
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 monthly,
use:
alter table
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.
insert into
pos_data (start_date, store_id, inventory_id, qty_sold)
values
('15-NOV-07', 1, 1, 1);
SELECT
TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM
Remote DBA_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
PARTITION_NAME
HIGH_VALUE
POS_DATA_P0
TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
POS_DATA_P1
TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P81
TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P84
TO_DATE(' 2015-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 command:
alter table
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
partitioning option.
Interval
Partitioning for Oracle
By Ben Prusinski
Interval
partitions build upon the range partitioning for Oracle 11g. Interval partitioning resolves the limitations built into range partitioning
when a specific range is unknown by the developer or DBA creating the
partitions for the table. It tells Oracle to automatically setup new
partitions for a particular interval when data inserted to tables are
greater than the range partitions.
As such
the requirement of interval partitioning dictates that at least one
range partition is specified. Once the range partitioning key is given
for the high value of the range partitions, this transition point is
used as the baseline to create interval partitions beyond this point.
The nice thing
about the new interval partitioning feature for Oracle 11g is that it
eases the management of new partitions for the busy Oracle DBA or
development staff. The following exercise will demonstrate how interval
partitioning works with Oracle 11g. First we will
create a new table to use for interval partitioning:
SQL> CREATE TABLE
sales_interval
2 (product_id
NUMBER(6),
3 customer_id
NUMBER,
4 time_id
DATE,
5 channel_info
CHAR(1),
6 promo_id
NUMBER(6),
7 qty_sold NUMBER(3),
8 amt_sold
NUMBER(10,2)
9 )
10 PARTITION BY RANGE (time_id)
11 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
12 (PARTITION
t0 VALUES LESS THAN (TO_DATE('1-1-2005','DD-MM-YYYY')),
13
PARTITION t1 VALUES LESS THAN (TO_DATE('1-1-2006','DD-MM-YYYY')),
14
PARTITION t2 VALUES LESS THAN (TO_DATE('1-7-2006','DD-MM-YYYY')),
15
PARTITION t3 VALUES LESS THAN (TO_DATE('1-1-2007','DD-MM-YYYY')) );
Table created.
Figure
1.8: Interval Partitioning Example with Oracle 11g
The above
statement creates a table with four new interval based partitions using
a one month period for the width of the interval with January 1, 2015 as the transition point with t3 partition as the high bound and
the other partitions (t0-t2) as the range section with all partitions
above it occur in the interval range. However, like other forms of
partitioning there are some limitations as follows.
Interval
partitioning restrictions include:
-
Index
Organized tables (IOTs) are not supported by interval partitioning.
-
Domain
index cannot be created on interval partitioned tables.
-
Only
one partitioning key column can be set for the interval partition
and it must be either a DATE or NUMBER data type.