Database Reference
In-Depth Information
The amount of redo generated via the single DDL statement is less than half the amount generated by the
multiple partition operation statements and consumes less than half the CPU time. 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.
Cascade Truncate
Starting with Oracle 12
c
you can truncate parent/child tables in tandem as a single atomic DDL statement. While the
truncate cascade is taking place, any queries issued against the parent/child table combination are always presented
with a read consistent view of the data. Meaning that the data in the parent/child tables will either be seen as both
tables populated or both tables truncated.
The truncate cascade functionality is initiated with a
TRUNCATE ... CASCADE
statement on the parent table. For
the cascading truncate to take place, any child tables must be defined with the foreign key relational constraint of
ON
DELETE CASCADE
. What does cascade truncate have to do with partitioning? In a reference partitioned table, you can
truncate a parent table partition and have it cascade to the child table partition in one transaction.
Let's look at an example of this. Applying the
TRUNCATE ... CASCADE
functionality to reference partitioned tables,
the parent
ORDERS
table is created here and the
ORDER_LINE_ITEMS
table is created with
ON DELETE CASCADE
applied to
the foreign key constraint:
EODA@ORA12CR1> create table orders
2 (
3 order# number primary key,
4 order_date date,
5 data varchar2(30)
6 )
7 PARTITION BY RANGE (order_date)
8 (
9 PARTITION part_2014 VALUES LESS THAN (to_date('01-01-2015','dd-mm-yyyy')) ,
10 PARTITION part_2015 VALUES LESS THAN (to_date('01-01-2016','dd-mm-yyyy'))
11 )
12 /
Table created.
EODA@ORA12CR1> insert into orders values
2 ( 1, to_date( '01-jun-2014', 'dd-mon-yyyy' ), 'xyz' );
1 row created.
EODA@ORA12CR1> insert into orders values
2 ( 2, to_date( '01-jun-2015', 'dd-mon-yyyy' ), 'xyz' );
1 row created.
And now we'll create the
ORDER_LINE_ITEMS
table, ensuring we include the
ON DELETE CASCADE
clause:
EODA@ORA12CR1> create table order_line_items
2 (
3 order# number,
4 line# number,
5 data varchar2(30),
6 constraint c1_pk primary key(order#,line#),
7 constraint c1_fk_p foreign key(order#) references orders on delete cascade
8 ) partition by reference(c1_fk_p)
9 /
