Cascade Functionality for TRUNCATE and EXCHANGE Partition Oracle Database 12C release 1 (12.1)

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

cascade_truncate

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

cascade_exchange

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

cascade_exchange_nonpartitioned

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

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *