Database Reference
In-Depth Information
Ease of Maintenance Features
At the beginning of this chapter I stated the goal was to provide a practical guide to implement applications with
partitioning, and that I wouldn't be focusing so much on administration. However, there are a few new administrative
features available starting with Oracle 12
c
that deserve some discussion, namely:
•
Multiple partition maintenance operations
•
Cascade exchange
•
Cascade delete
These features have a positive impact in terms of ease of maintenance, data integrity, and performance.
Therefore it's important to be aware of these features when implementing partitioning.
Multiple Partition Maintenance Operations
This feature eases the administration of partitioning and in some scenarios reduces the database resources required
to perform maintenance operations. Prior to Oracle 12
c
, when performing partition operations such as adding
a partition, you were only allowed to work with one partition at a time. For example, take the following range
partitioned table:
EODA@ORA12CR1> create table p_table
2 (a int)
3 partition by range (a)
4 (partition p1 values less than (1000),
5 partition p2 values less than (2000));
Table created.
Prior to 12
c
, if you wanted to add two partitions to a table, it was done with two separate SQL statements:
EODA@ORA12CR1> alter table p_table add partition p3 values less than (3000);
Table altered.
EODA@ORA12CR1> alter table p_table add partition p4 values less than (4000);
Table altered.
Starting with Oracle 12
c
, you can perform multiple partition operations in one statement. The prior code can be
run as follows:
EODA@ORA12CR1> alter table p_table add
2 partition p3 values less than (3000),
3 partition p4 values less than (4000);
Table altered.
■
In addition to adding partitions, multiple partition maintenance operations can be applied to dropping, merging,
splitting, and truncating.
Note
