Truncate Partition

A recent posting on the OTN database forum (which I didn’t answer correctly on my first attempt) raised the problem of truncating partitions when there are referential integrity constraints in place. Let’s start with a demonstration of the problem:

create table parent (
	id 	number,
	v1	varchar2(10),
	padding	varchar2(100),
	constraint	p_pk primary key(id) using index local
)
partition by range(id) (
	partition p1000 values less than (1000),
	partition p2000 values less than (2000),
	partition p3000 values less than (3000),
	partition p4000 values less than (4000),
	partition p5000 values less than (5000)
)
;

create table child (
	id_p		number,
	seq_c		number,
	v1		varchar2(10),
	padding		varchar2(100),
	constraint	c_fk_p foreign key (id_p) references parent,
	constraint	c_pk primary key (id_p, seq_c) using index local
)
partition by range(id_p) (
	partition p1000 values less than (1000),
	partition p2000 values less than (2000),
	partition p3000 values less than (3000),
	partition p4000 values less than (4000),
	partition p5000 values less than (5000)
)
;

insert into parent
select
	rownum - 1,
	rpad(rownum - 1,10),
	rpad('x',100)
from
	all_objects
where
	rownum <= 4000  -- > comment to avoid wordpress format issue
;

insert into child
select
	id,
	val,
	v1,
	padding
from
	parent,
	(
		select 1 val from dual
		union all
		select 2 val from dual
		union all
		select 3 val from dual
		union all
		select 4 val from dual
	)
;

-- gather some stats on the two tables.

alter table child  truncate partition p4000;
alter table parent truncate partition p4000;

The problem is that you can’t truncate the parent partition because Oracle thinks there may be related data in the child partition – despite the fact that the internal code could be written to see that the partition’s data segment was “new”. The output from the two truncate commands is as follows:

-- the call for the child works

Table truncated.

alter table parent truncate partition p4000
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

You can’t truncate the parent partition unless you disable the foreign key constraint – and it might take quite a long time to re-enable it since we usually only worry about partitioning for large objects.

There is a slightly surprising workaround that might be good enough. If you set the foreign key constraint to novalidate, rather than disable, you can tell Oracle to exchange partitions without checking the validity of the foreign key constraint. So:

create table parent_ex (
	id 	number,
	v1	varchar2(10),
	padding	varchar2(100),
	constraint	px_pk primary key(id)
)
;

create table child_ex (
	id_p		number,
	seq_c		number,
	v1		varchar2(10),
	padding		varchar2(100),
	constraint	cx_fk_px foreign key (id_p) references parent_ex,
	constraint	cx_pk primary key (id_p, seq_c)
)
;

alter table child modify constraint c_fk_p
		rely
		enable
		novalidate
;

alter table child_ex modify constraint cx_fk_px
		rely
		enable
		novalidate
;

prompt	============================================================
prompt	We can now exchange the child and parent partitions we want.
prompt	============================================================

alter table child exchange partition p4000 with table child_ex
	including indexes
	without validation
;

alter table parent exchange partition p4000 with table parent_ex
	including indexes
	without validation
;

Having done the two exchanges, we have two empty partitions in the partitioned table and we can truncate the resulting parent_ex and child_ex tables. If we wanted we could also modify the referential integrity constraint back to validate mode – although this would take some time, of course, and we might just decide that it wasn’t necessary because (we believe) all the old data would be correct, and we may only be worried about making sure that new data will be checked.

The downside to this method is that we need to keep (or recreate) a pair of tables in the right tablespace so that we can do the exchange when we need to; but that doesn’t seem to be a terrible price to pay for the ability to “truncate” a single partition without disabling referential integrity constraints.