In Oracle 12c new word CASCADE can be specified for TRUNCATE and EXCHANGE command for “master” partitioned tables which are referenced by enabled ON DELETE CASCADE referential constraint.
Truncate Cascade for partitioned tables
If you specify TRUNCATE … CASCADE for “master” partitioned table then Oracle Database truncates all child reference tables that reference master partitioned table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child reference tables, grandchild reference tables, and so on, using the specified options.
Following options are available for “master” partitioned tables:
1. In this case child, grandchild tables don’t need to be partitioned
TRUNCATE <table_name> CASCADE
2. In this case child, grandchild tables must be reference partitioned tables
ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name> CASCADE ALTER TABLE <table_name> TRUNCATE SUBPARTITION <subpartition_name> CASCADE
Example for TRUNCATE PARTITION CASCADE, TRUNCATE SUBPARTITION CASCADE, TRUNCATE TABLE
Three tables are created to present how it works:
- MASTER_REF_TBL – partitioned by RANGE, subpartitioned by RANGE
- CHILD_REF_TBL1 – REFRENCE partitioned with constraint ON DELETE CASCADE to MASTER_REF_TBL
- CHILD_REF_TBL1 – REFRENCE partitioned with constraint ON DELETE CASCADE to MASTER_REF_TBL
create table master_ref_tbl ( id1 number, id2 number, constraint master_ref_tbl_pk primary key(id1) ) partition by range(id1) subpartition by range(id2) ( partition p1 values less than(10) ( subpartition s1 values less than(5), subpartition s2 values less than(10) ), partition p2 values less than(20) ( subpartition s3 values less than(15), subpartition s4 values less than(20) ) ); create table child_ref_tbl1 ( id1 number not null, id2 number not null, constraint child_ref_tbl1_pk primary key(id1), constraint child_ref_tbl1_fk foreign key (id2) references master_ref_tbl(id1) ON DELETE CASCADE ) partition by REFERENCE(child_ref_tbl1_fk); create table child_ref_tbl2 ( id1 number not null, id2 number not null, constraint child_ref_tbl2_pk primary key(id1), constraint child_ref_tbl2_fk foreign key (id2) references master_ref_tbl(id1) ON DELETE CASCADE ) partition by REFERENCE(child_ref_tbl2_fk);
Once created you can see that the tables has got following partitions, subpartitions:
MASTER_REF_TBL
- partition P1
- subpartition S1
- subpartition S2
- partition P2
- subpartition S3
- subpartition S4
CHILD_REF_TBL1
- partition S1 – reference via FK to subpartition MASTER_REF_TBL(S1)
- partition S2 – reference via FK to subpartition MASTER_REF_TBL(S2)
- partition S3 – reference via FK to subpartition MASTER_REF_TBL(S3)
- partition S4 – reference via FK to subpartition MASTER_REF_TBL(S4)
CHILD_REF_TBL2
- partition S1 – reference via FK to subpartition MASTER_REF_TBL(S1)
- partition S2 – reference via FK to subpartition MASTER_REF_TBL(S2)
- partition S3 – reference via FK to subpartition MASTER_REF_TBL(S3)
- partition S4 – reference via FK to subpartition MASTER_REF_TBL(S4)
Let’s load data into the tables and check partitions, subpartitions for both tables
begin
for i in 0..19
loop
insert into master_ref_tbl values(i, i);
insert into child_ref_tbl1 values(i, i);
insert into child_ref_tbl2 values(i, i);
end loop;
commit;
end;
/
--collecting statistics to have proper NUM_ROWS value
--in dictionary views
exec dbms_stats.gather_table_stats(user, 'MASTER_REF_TBL');
exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL1');
exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL2');
select table_name, partition_name, num_rows
from dba_tab_partitions
where table_name='MASTER_REF_TBL'
order by partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- --------------- ---------
MASTER_REF_TBL P1 10
MASTER_REF_TBL P2 10
select table_name, partition_name, subpartition_name, num_rows
from dba_tab_subpartitions
where table_name='MASTER_REF_TBL'
order by partition_name, subpartition_position;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------ ---------
MASTER_REF_TBL P1 S1 5
MASTER_REF_TBL P1 S2 5
MASTER_REF_TBL P2 S3 5
MASTER_REF_TBL P2 S4 5
select table_name, partition_name, num_rows
from dba_tab_partitions
where table_name in ('CHILD_REF_TBL1', 'CHILD_REF_TBL2')
order by table_name, partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- ------------------ ---------
CHILD_REF_TBL1 S1 5
CHILD_REF_TBL1 S2 5
CHILD_REF_TBL1 S3 5
CHILD_REF_TBL1 S4 5
CHILD_REF_TBL2 S1 5
CHILD_REF_TBL2 S2 5
CHILD_REF_TBL2 S3 5
CHILD_REF_TBL2 S4 5
Truncate Cascade – partition level
First test – TRUNCATE table MASTER_REF_TBL on partition level P1 with CASCADE option. It will truncate partitions S1 and S2 of table CHILD_REF_TBL1 and CHILD_REF_TBL2.
alter table master_ref_tbl
TRUNCATE PARTITION p1 CASCADE;
--collecting statistics to have proper NUM_ROWS value
--in dictionary views
exec dbms_stats.gather_table_stats(user, 'MASTER_REF_TBL');
exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL1');
exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL2');
select table_name, partition_name, num_rows
from dba_tab_partitions
where table_name='MASTER_REF_TBL'
order by partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- --------------- ---------
MASTER_REF_TBL P1 0
MASTER_REF_TBL P2 10
select table_name, partition_name, subpartition_name, num_rows
from dba_tab_subpartitions
where table_name='MASTER_REF_TBL'
order by partition_name, subpartition_position;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------ ---------
MASTER_REF_TBL P1 S1 0
MASTER_REF_TBL P1 S2 0
MASTER_REF_TBL P2 S3 5
MASTER_REF_TBL P2 S4 5
select table_name, partition_name, num_rows
from dba_tab_partitions
where table_name in ('CHILD_REF_TBL1', 'CHILD_REF_TBL2')
order by table_name, partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- ------------------ ---------
CHILD_REF_TBL1 S1 0
CHILD_REF_TBL1 S2 0
CHILD_REF_TBL1 S3 5
CHILD_REF_TBL1 S4 5
CHILD_REF_TBL2 S1 0
CHILD_REF_TBL2 S2 0
CHILD_REF_TBL2 S3 5
CHILD_REF_TBL2 S4 5
Truncate Cascade – subpartition level
Next test – TRUNCATE table MASTER_REF_TBL on subpartition S4 level with CASCADE option. It will truncate partition S4 of table CHILD_REF_TBL1 and table CHILD_REF_TBL2.
alter table master_ref_tbl
TRUNCATE SUBPARTITION s4 CASCADE;
--collecting statistics to have proper NUM_ROWS value
--in dictionary views
exec dbms_stats.gather_table_stats(user, 'MASTER_REF_TBL');
exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL1');
exec dbms_stats.gather_table_stats(user, 'CHILD_REF_TBL2');
select table_name, partition_name, num_rows
from dba_tab_partitions
where table_name='MASTER_REF_TBL'
order by partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- --------------- ---------
MASTER_REF_TBL P1 0
MASTER_REF_TBL P2 5
select table_name, partition_name, subpartition_name, num_rows
from dba_tab_subpartitions
where table_name='MASTER_REF_TBL'
order by partition_name, subpartition_position;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------ ---------
MASTER_REF_TBL P1 S1 0
MASTER_REF_TBL P1 S2 0
MASTER_REF_TBL P2 S3 5
MASTER_REF_TBL P2 S4 0
select table_name, partition_name, num_rows
from dba_tab_partitions
where table_name in ('CHILD_REF_TBL1', 'CHILD_REF_TBL2')
order by table_name, partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- ------------------ ---------
CHILD_REF_TBL1 S1 0
CHILD_REF_TBL1 S2 0
CHILD_REF_TBL1 S3 5
CHILD_REF_TBL1 S4 0
CHILD_REF_TBL2 S1 0
CHILD_REF_TBL2 S2 0
CHILD_REF_TBL2 s3 5
CHILD_REF_TBL2 S4 0
Truncate Cascade – table level
Last test – TRUNCATE table MASTER_REF_TBL on table level with CASCADE, but first I’m going to add NON partitioned table CHILD_REF_TBL3
create table child_ref_tbl3 ( id1 number not null, id2 number not null, constraint child_ref_tbl3_pk primary key(id1), constraint child_ref_tbl3_fk foreign key (id2) references master_ref_tbl(id1) ON DELETE CASCADE ); --index master_ref_tbl_pk is global --so needs to be rebuild to avoid errors --during insert into child_ref_tbl3 alter index master_ref_tbl_pk rebuild; insert into child_ref_tbl3 values(10,10); commit;
In this moment both TRUNCATE PARTITION .. CASCADE and TRUNCATE SUBPARTITION CASCADE can raise exception if truncated partitions/subpartitions of table MASTER_REF_TBL has got some data.
--partition p1 is empty so nothing happens
alter table master_ref_tbl
TRUNCATE PARTITION p1 CASCADE;
--subpartition s4 is empty so nothing happens
alter table master_ref_tbl
TRUNCATE SUBPARTITION s4 CASCADE;
alter table master_ref_tbl
TRUNCATE PARTITION p2 CASCADE;
ORA-02266: unique/primary keys
in table referenced by enabled foreign keys
alter table master_ref_tbl
TRUNCATE SUBPARTITION s3 CASCADE;
ORA-02266: unique/primary keys
in table referenced by enabled foreign key
but TRUNCATE .. CASCADE will always work 🙂 no matter if child tables are partitioned or not.
TRUNCATE TABLE master_ref_tbl CASCADE; select count(*) from master_ref_tbl union all select count(*) from child_ref_tbl1 union all select count(*) from child_ref_tbl2 union all select count(*) from child_ref_tbl3; COUNT(*) ---------- 0 0 0 0
Exchange Cascade for partitioned tables
If you specify EXCHANGE … CASCADE for “master” partitioned table then Oracle Database cascade exchange operation to child reference partitioned table that reference “master” partitioned table with an enabled ON DELETE CASCADE referential constraint.
This is a recursive operation that will exchange all affected partitions of reference partitioned table, grandchild reference partitioned table, and so on, using the specified options.
This option is supported if:
- parent key is referenced only by one single dependent child table.
- foreign key constraint must be defined as ON DELETE CASCADE for dependent child table
- dependent child table must be reference partitioned
- all above requirements must be true for grandchildren table …
Other features
- The CASCADE option is ignored if it is specified for a table that does not have reference partitioned children
- The CASCADE options are off by default so they do not affect Oracle Database compatibility
Following options are available for “master” partitioned tables:
ALTER TABLE <table_name> EXCHANGE PARTITION <partition_name> CASCADE; ALTER TABLE <table_name> EXCHANGE SUBPARTITION <subpartition_name> CASCADE;
Exchange subpartition cascade
First I have created three test tables which are using RANGE and REFERENCE partitioning
- MASTER_TBL – partitioned by RANGE, subpartitioned by RANGE
- CHILD_TBL – REFERENCE partitioned with constraint ON DELETE CASCADE to MASTER_TBL
- GRANDCHILD_TBL – REFERENCE partitioned with constraint ON DELETE CASCADE to CHILD_TBL
create table master_tbl ( id1 number, id2 number, id3 varchar2(10), constraint master_tbl_pk primary key(id1) ) partition by range(id1) subpartition by range(id2) ( partition p1 values less than(10) ( subpartition s1 values less than(5), subpartition s2 values less than(10) ), partition p2 values less than(20) ( subpartition s3 values less than(15), subpartition s4 values less than(20) ) ); create table child_tbl ( id1 number, id2 number not null, id3 varchar2(10), constraint child_tbl_pk primary key(id1), constraint child_tbl_fk foreign key(id2) references master_tbl ON DELETE CASCADE ) partition by REFERENCE(child_tbl_fk); create table grandchild_tbl ( id1 number, id2 number not null, id3 varchar2(10), constraint grandchild_tbl_pk primary key(id1), constraint grandchild_tbl_fk foreign key(id2) references child_tbl ON DELETE CASCADE ) partition by REFERENCE(grandchild_tbl_fk);
Once created you can see that the tables has got following partitions, subpartitions:
MASTER_TBL
- partition P1
- subpartition S1
- subpartition S2
- partition P2
- subpartition S3
- subpartition S4
CHILD_TBL
- partition S1 – reference via FK to subpartition MASTER_TBL(S1)
- partition S2 – reference via FK to subpartition MASTER_TBL(S2)
- partition S3 – reference via FK to subpartition MASTER_TBL(S3)
- partition S4 – reference via FK to subpartition MASTER_TBL(S4)
GRANDCHILD_TBL
- partition S1 – reference via FK to subpartition CHILD_TBL(S1)
- partition S2 – reference via FK to subpartition CHILD_TBL(S2)
- partition S3 – reference via FK to subpartition CHILD_TBL(S3)
- partition S4 – reference via FK to subpartition CHILD_TBL(S4)
let’s load data to tables to test tables
begin
for i in 0..19
loop
insert into master_tbl values(i, i, 'm_'||i);
insert into child_tbl values(i, i, 'c_'||i);
insert into grandchild_tbl values(i, i, 'g_'||i);
end loop;
commit;
end;
/
exec dbms_stats.gather_table_stats(user, 'MASTER_TBL');
exec dbms_stats.gather_table_stats(user, 'CHILD_TBL');
exec dbms_stats.gather_table_stats(user, 'GRANDCHILD_TBL');
select table_name, partition_name, num_rows
from dba_tab_partitions
where table_name='MASTER_TBL'
order by partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
----------- --------------- ---------
MASTER_TBL P1 10
MASTER_TBL P2 10
select table_name, partition_name, subpartition_name, num_rows
from dba_tab_subpartitions
where table_name='MASTER_TBL'
order by partition_name, subpartition_position;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
----------- --------------- ------------------ --------
MASTER_TBL P1 S1 5
MASTER_TBL P1 S2 5
MASTER_TBL P2 S3 5
MASTER_TBL P2 S4 5
select table_name, partition_name, num_rows
from dba_tab_partitions
where table_name in ('CHILD_TBL', 'GRANDCHILD_TBL')
order by table_name, partition_position;
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- --------------- ---------
CHILD_TBL S1 5
CHILD_TBL S2 5
CHILD_TBL S3 5
CHILD_TBL S4 5
GRANDCHILD_TBL S1 5
GRANDCHILD_TBL S2 5
GRANDCHILD_TBL S3 5
GRANDCHILD_TBL S4 5
now it’s time to prepare tables to exchange. I have created three non partitioned tables:
- E_MASTER_TBL
- E_CHILD_TBL – with constraints ON DELETE CASCADE to E_MASTER_TBL
- E_GRANDCHILD_TBL – with constraint ON DELETE CASCADE to E_CHILD_TBL
create table e_master_tbl ( id1 number, id2 number, id3 varchar2(10), constraint e_master_tbl_pk primary key(id1) ); create table e_child_tbl ( id1 number, id2 number not null, id3 varchar2(10), constraint e_child_tbl_pk primary key(id1), constraint e_child_tbl_fk foreign key(id2) references e_master_tbl on delete cascade ); create table e_grandchild_tbl ( id1 number, id2 number not null, id3 varchar2(10), constraint e_grandchild_tbl_pk primary key(id1), constraint e_grandchild_tbl_fk foreign key(id2) references e_child_tbl on delete cascade );
Lets’ load some dummy data to exchange tables.
begin insert into e_master_tbl values(1, 1, 'e_m_1'); insert into e_child_tbl values(1, 1, 'e_c_1'); insert into e_grandchild_tbl values(1, 1, 'e_gc_1'); commit; end; / select count(*) from e_master_tbl union all select count(*) from e_child_tbl union all select count(*) from e_grandchild_tbl; COUNT(*) ---------- 1 1 1
MASTER_TABLE has got subpartitions so the only option that will work here is EXCHANGE SUBPARTITIONS with CASCADE option. Now data are replaced between tables:
- MASTER_TBL(subpartition s1) and E_MASTER_TBL
- CHILD_TBL(supartition s1) and E_CHILD_TBL
- GRANDCHILD_TBL(partition s1) and E_GRANDCHILD_TBL
current data in test tables
select count(*) from master_tbl subpartition(s1) union all select count(*) from child_tbl partition(s1) union all select count(*) from grandchild_tbl partition(s1); COUNT(*) ---------- 5 5 5
so it’s time to replace it with exchange tables
alter table master_tbl EXCHANGE SUBPARTITION s1 with table e_master_tbl excluding indexes without validation CASCADE;
now data are replaced on all levels
select count(*) from e_master_tbl union all select count(*) from e_child_tbl union all select count(*) from e_grandchild_tbl; COUNT(*) ---------- 5 5 5 select count(*) from master_tbl subpartition(s1) union all select count(*) from child_tbl partition(s1) union all select count(*) from grandchild_tbl partition(s1); COUNT(*) ---------- 1 1 1
Exchange partition cascade
Let’s cleanup old exmple
drop table grandchild_tbl; drop table child_tbl; drop table master_tbl; drop table e_grandchild_tbl; drop table e_child_tbl; drop table e_master_tbl;
Quick example similar to subpartitions but this time much shorter. This time table MASTER_TBL hasn’t got subpartitions.
create table master_tbl ( id1 number, id2 number, id3 varchar2(10), constraint master_tbl_pk primary key(id1) ) partition by range(id1) ( partition p1 values less than(10), partition p2 values less than(20) ); create table child_tbl ( id1 number, id2 number not null, id3 varchar2(10), constraint child_tbl_pk primary key(id1), constraint child_tbl_fk foreign key(id2) references master_tbl ON DELETE CASCADE ) partition by REFERENCE(child_tbl_fk); create table e_master_tbl ( id1 number, id2 number, id3 varchar2(10), constraint e_master_tbl_pk primary key(id1) ); create table e_child_tbl ( id1 number, id2 number not null, id3 varchar2(10), constraint e_child_tbl_pk primary key(id1), constraint e_child_tbl_fk foreign key(id2) references e_master_tbl ON DELETE CASCADE );
Load data to test and exchange tables
--load data to test tables begin for i in 0..19 loop insert into master_tbl values(i, i, 'm_'||i); insert into child_tbl values(i, i, 'c_'||i); end loop; commit; end; / --and to exchange tables begin insert into e_master_tbl values(1, 1, 'e_m_1'); insert into e_child_tbl values(1, 1, 'e_c_1'); commit; end; / select count(*) from e_master_tbl union all select count(*) from e_child_tbl; COUNT(*) ---------- 1 1 select count(*) from master_tbl partition(p1) union all select count(*) from child_tbl partition(p1); COUNT(*) ---------- 10 10
It’s time to exchange partition cascade
alter table master_tbl EXCHANGE PARTITION p1 with table e_master_tbl excluding indexes without validation CASCADE;
As you can see data are replaced between test and exchange tables
select count(*) from e_master_tbl union all select count(*) from e_child_tbl; COUNT(*) ---------- 10 10 select count(*) from master_tbl partition(p1) union all select count(*) from child_tbl partition(p1); COUNT(*) ---------- 1 1
Have a fun 🙂
Tomasz