Partitioning - Expert Oracle Database Architecture - page 647

Performing multiple maintenance partition operations in one DDL statement is particularly advantageous for
splitting partitions and thus deserves more discussion. Think about what happens in Oracle 11 g in the scenario of
where you need to split a P2014 yearly partition into four quarterly partitions: Q1 , Q2 , Q3 , and Q4 . You would have to
split the P2014 with three separate DDL statements; each operation requiring a scan of all of the rows in the partition
being split, Oracle determining which partition the row should be inserted into, and then inserting. Having to split
and re-split multiple times consumes many more resources than it would if you could simply split multiple partitions
as one operation. A small example will illustrate this. Let's set this up by creating a table and loading it with data:
EODA@ORA12CR1> CREATE TABLE sales(
2 sales_id int
3 ,s_date date)
4 PARTITION BY RANGE (s_date)
5 (PARTITION P2014 VALUES LESS THAN (to_date('01-jan-2015','dd-mon-yyyy')));
Table created.
EODA@ORA12CR1> insert into sales
2 select level, to_date('01-jan-2014','dd-mon-yyyy') + ceil(dbms_random.value(1,364))
3 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:
EODA@ORA12CR1> create or replace function get_stat_val( p_name in varchar2 ) return number
2 as
3 l_val number;
4 begin
5 select b.value
6 into l_val
7 from v$statname a, v$mystat b
8 where a.statistic# = b.statistic#
9 and a.name = p_name;
10 return l_val;
11 end;
12 /
Function created.
Now we'll use the pre-12 c method of splitting a partition into multiple partitions and measure the amount of redo
our session generates. Using GET_STAT_VAL , we get the current value for the redo statistic:
EODA@ORA12CR1> var r1 number
EODA@ORA12CR1> exec :r1 := get_stat_val('redo size');
PL/SQL procedure successfully completed.
And using DBMS_UTILITY , we'll record the current CPU time:
EODA@ORA12CR1> var c1 number
EODA@ORA12CR1> exec :c1 := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.