Database Reference
In-Depth Information
Next, using the pre-12
c
syntax, the
P2014
partition is split into four partitions with three separate DDL
statements:
EODA@ORA12CR1> alter table sales split partition
2 P2014 at (to_date('01-apr-2014','dd-mon-yyyy'))
3 into (partition Q1, partition Q2);
Table altered.
EODA@ORA12CR1> alter table sales split partition
2 Q2 at (to_date('01-jul-2014','dd-mon-yyyy'))
3 into (partition Q2, partition Q3);
Table altered.
EODA@ORA12CR1> alter table sales split partition
2 Q3 at (to_date('01-oct-2014','dd-mon-yyyy'))
3 into (partition Q3, partition Q4);
Table altered.
Now we'll display the difference in the redo size statistic and CPU time:
EODA@ORA12CR1> exec dbms_output.put_line(get_stat_val('redo size') - :r1);
4747712
EODA@ORA12CR1> exec dbms_output.put_line(dbms_utility.get_cpu_time - :c1);
16
A sizable amount of redo has been generated due to the multiple split operations, resulting in many insert
statements as Oracle splits the partition multiple times and re-inserts rows.
Next we'll run the exact same test except using the new 12
c
syntax, we'll split the
P2014
partition into four
partitions in one DDL statement (re-creating and populating the table not shown here for brevity):
EODA@ORA12CR1> var r1 number
EODA@ORA12CR1> exec :r1 := get_stat_val('redo size');
PL/SQL procedure successfully completed.
EODA@ORA12CR1> var c1 number
EODA@ORA12CR1> exec :c1 := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
EODA@ORA12CR1> alter table sales split partition P2014
2 into (partition Q1 values less than (to_date('01-apr-2014','dd-mon-yyyy')),
3 partition Q2 values less than (to_date('01-jul-2014','dd-mon-yyyy')),
4 partition Q3 values less than (to_date('01-oct-2014','dd-mon-yyyy')),
5 partition Q4);
Table altered.
EODA@ORA12CR1> exec dbms_output.put_line(get_stat_val('redo size') - :r1);
2099288
EODA@ORA12CR1> exec dbms_output.put_line(dbms_utility.get_cpu_time - :c1);
6
