Drop parent partition

Here’s a dirty trick for those of you who are having problems with “partition-wise” referential integrity on partitioned tables.

If you’ve created a pair of partitioned tables, parent and child say, with referential integrity declared between them, then you’ve probably discovered that you have to set the foreign key constraint on the child table to novalidate if you want to use “partition exchange loading” (as Oracle now calls it) to get data into the child table.

If you’ve got  to the point where you decide to drop a pair of old partitions, you may have tried the following:

SQL> alter table child drop partition p2000;

Table altered.

SQL> alter table parent drop partition p2000;
alter table parent drop partition p2000
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Because they’re your tables you know that you’ve defined them so that data in parent partition(N) and child partition(N) are perfectly synchronised – but Oracle doesn’t know this, so you can’t drop the parent partition unless you actually disable the foreign key constraint.  And if the tables are large, you may find it’s rather expensive to re-enable the constraint after the drop.

It’s easy to be caught out whilst testing this, as you can drop the parent partition if it’s never held any data – so a quick “create tables, drop partitions” test will give you a spurious success if you didn’t bother to insert any data into the tables. However, this observation supplies a clue to a dirty trick. You can keep the foreign key constraint enabled with the novalidate option set and do the following:

rem
rem     Script:         c_pt_fk.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sept 2001
rem

create table parent_clone as select * from parent where 1 = 0;
alter table parent_clone add constraint pc_pk primary key(id);  -- modify as necessary

alter table parent
	exchange partition p2000 with table parent_clone
	including indexes without validation;

alter table parent drop partition p2000

You can swap the full partition with a “never used” table, and then drop the resulting empty partition.

Footnote:

11g is expected to include a special ‘ref partitioning’ feature to deal with this specific issue. [Update: but it doesn’t apply if your child table is partitioned on the parent primary key and you’ve copied the key down – which is what I’d hope to see in most (if not all) cases anyway.]

Footnote 2:

Bear in mind that, for the duration of the partition maintenance, any queries that would normally be optimised to do a partition-wise join will see the two tables as having different numbers of partitions, which means the partition-wise join feature will be disabled – with dire consequences on your workload and performance.

So make sure that you don’t let anyone run the wrong queries while you’re doing this type of partition maintenance.

Update (Sep 2020)

In footnote 1 I made a comment about ref partitioning not working properly if you’ve designed your referential integrity so that the parent key is copied down to the child table and the child table is partitioned on the  parent key. In a comment to an article written in 2012, I’ve pointed out that I couldn’t find the model I had constructed to demonstrate this point, so it’s possible that (a) I made a mistake constructing my model or (b) the version of 11g I was using had a short-term restriction that has since been removed.