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 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. Oracle allows you to combine more than one partition maintenance operation in one DDL statement. Consider the following example:

$ sqlplus eoda/foo@PDB1
SQL> create table p_table(a int)partition by range (a)
(partition p1 values less than (1000), partition p2 values less than (2000));
Table created.

Now say you want to add multiple partitions to the table that was just created. Oracle allows you to perform multiple partition operations in one statement:

SQL> alter table p_table add
partition p3 values less than (3000),
partition p4 values less than (4000);Table altered.

Note In addition to adding partitions, multiple partition maintenance operations can be applied to dropping, merging, splitting, and truncating.

Performing multiple maintenance partition operations in one DDL statement is particularly advantageous for splitting partitions and thus deserves more discussion. A small example will illustrate this. Let’s set this up by creating a table and loading it with data:

SQL> CREATE TABLE sales(sales_id int,s_date date)PARTITION BY RANGE (s_date)
(PARTITION P2021 VALUES LESS THAN (to_date(’01-jan-2022′,’dd-mon-­yyyy’))); Table created.
SQL> insert into sales
select level, to_date(’01-jan-2021′,’dd-mon-yyyy’) + ceil(dbms_random. value(1,364))
from dual connect by level < 100000; 99999 rows created.

Next, we create a small utility function to help us measure the resources consumed while performing an operation:

SQL> create or replace function get_stat_val( p_name in varchar2 ) return number
asl_val number;beginselect b.valueinto l_valfrom v$statname a, v$mystat b
where a.statistic# = b.statistic#and a.name = p_name;return l_val;end;/
Function created.

Next, we’ll split the P2021 partition into four partitions in one DDL statement and measure the resources consumed:

SQL> var r1 number
SQL> exec :r1 := get_stat_val(‘redo size’);
PL/SQL procedure successfully completed.
SQL> var c1 number
SQL> exec :c1 := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
SQL> alter table sales split partition P2021
into (partition Q1 values less than (to_date(’01-apr-2021′,’dd-mon-­yyyy’)), partition Q2 values less than (to_date(’01-jul-2021′,’dd-mon-­yyyy’)),
partition Q3 values less than (to_date(’01-oct-2021′,’dd-mon-­yyyy’)), partition Q4);Table altered.
SQL> set serverout on
SQL> exec dbms_output.put_line(get_stat_val(‘redo size’) – :r1); 47068
SQL> exec dbms_output.put_line(dbms_utility.get_cpu_time – :c1); 12

The amount of redo generated via the single DDL statement is relatively low. Depending on the number of partitions being split and if you’re updating indexes at the same time, the amount of redo generated and CPU consumed can be considerably less than when splitting the maintenance operations into multiple statements.