Databases Reference
In-Depth Information
Creating Partitions on Demand
As of Oracle Database 11g, you can instruct Oracle to add partitions to range-partitioned tables automatically. This
feature is known as interval partitioning. Oracle dynamically creates a new partition when data inserted exceed the
maximum bound of a range-partitioned table. The newly added partition is based on an interval that you specify
(hence, the name interval partitioning).
■
Tip
think of the interval as a rule you provide, stating how you want future partitions to be created.
Adding Yearly Partitions, Based on Date
Suppose, for instance, you have a range-partitioned table and want Oracle to add a partition automatically when
values are inserted above the highest value defined for the highest range. You can use the
INTERVAL
clause of the
CREATE TABLE
statement to instruct Oracle to add a partition automatically to the high end of a range-partitioned
table. The following example creates a table that initially has one partition, with a high value range of
01-01-2013
:
create table f_sales(
sales_amt number
,d_date_dtt date)
partition by range (d_date_dtt)
interval(numtoyminterval(1, 'YEAR'))
store in (p1_tbsp, p2_tbsp, p3_tbsp)
(partition p1 values less than (to_date('01-01-2013','dd-mm-yyyy'))
tablespace p1_tbsp);
The first partition is created in the
P1_TBSP
tablespace. As Oracle adds partitions, it assigns a new partition to the
tablespaces defined in the
STORE IN
clause (the program is supposed to store them in a round-robin fashion but isn't
always consistent).
■
With interval partitioning, you can specify only a single key column from the table, and it must be either
a
DATE
or a
NUMBER
data type. this is because the interval is mathematically added to these data types. You can't use
a
VARCHAR2
, as you can't add a number to a
VARCHAR2
data type.
Note
The interval in this example is one year, specified by the
INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
clause. If a
record is inserted into the table with a
D_DATE_DTT
value greater than or equal to 01-01-2013, Oracle automatically
adds a new partition to the high end of the table. You can check the details of the partition by running this SQL
statement:
set lines 132
col table_name form a10
col partition_name form a9
col part_pos form 999
col interval form a10
col tablespace_name form a12
col high_value form a30
--
