Database Reference
In-Depth Information
All values that are not explicitly in our list of values will go here. A word of caution on the use of
DEFAULT
:
once a list partitioned table has a
DEFAULT
partition, you cannot add any more partitions to it. So:
EODA@ORA12CR1> alter table list_example
2 add partition
3 part_4 values( 'CA', 'NM' );
alter table list_example
*
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists
We would have to remove the
DEFAULT
partition, then add
PART_4
, and then put the
DEFAULT
partition back. The
reason behind this is that the
DEFAULT
partition could have had rows with the list partition key value of
CA
or
NM
—they
would not belong in the
DEFAULT
partition after adding
PART_4
.
Interval Partitioning
Interval partitioning is a feature available in Oracle Database 11
g
Release 1 and above. It is very similar to range
partitioning described previously—in fact, it starts with a range partitioned table but adds a rule (the interval) to the
definition so the database knows how to add partitions in the future.
The goal of interval partitioning is to create new partitions for data—if, and only if, data exists for a given partition
and only when that data arrives in the database. In other words, to remove the need to pre-create partitions for
data, to allow the data itself to create the partition as it is inserted. To use interval partitioning, you start with a range
partitioned table without a
MAXVALUE
partition and specify an interval to add to the
upper bound
, the highest value
of that partitioned table to create a new range. You need to have a table that is range partitioned on a single column
that permits adding a
NUMBER
or
INTERVAL
type to it (e.g. a table partitioned by a
VARCHAR2
field cannot be interval
partitioned; there is nothing you can add to a
VARCHAR2
). You can use interval partitioning with any suitable existing
range partitioned table; that is, you can
ALTER
an existing range partitioned table to be interval partitioned, or you can
create one with the
CREATE TABLE
command.
For example, suppose you had a range partitioned table that said “anything strictly less than
01-JAN-2015
(data in
the year 2014 and before) goes into partition
P1—
and that was it. So it had one partition for all data in the year 2014 and
before. If you attempted to insert data for the year 2015 into the table, the insert would fail as demonstrated previously
in the section on range partitioning. With interval partitioning you can create a table and specify both a range (strictly
less than 01-JAN-2015) and an interval—say 1 month in duration—and the database would create monthly partitions
(a partition capable of holding exactly one month's worth of data) as the data arrived. The database would not
pre-create all possible partitions because that would not be practical. But, as each row arrived the database would see
whether the partition for the month in question existed. The database would create the partition if needed.
Here is an example of the syntax:
EODA@ORA12CR1> create table audit_trail
2 ( ts timestamp,
3 data varchar2(30)
4 )
5 partition by range(ts)
6 interval (numtoyminterval(1,'month'))
7 store in (users, example )
8 (partition p0 values less than
9 (to_date('01-01-1900','dd-mm-yyyy'))
10 )
11 /
Table created.
