Oracle Scratchpad

December 10, 2006

Drop parent partition

Filed under: Infrastructure,Partitioning — Jonathan Lewis @ 9:01 pm GMT Dec 10,2006

Here’s a dirty trick for those of you who are having problems with “partition0-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 cunning trick. You can keep the foreign key constraint enabled with the novalidate option set and do the following:

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.

7 Comments »

  1. Dear Jonathan,

    Following your advise more than two years ago, I’ve succeeded to exchange partitions with referentiel integrity. See (ask tom)

    http://govt.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:728425384831#60451928328887

    Best Regards

    Comment by Mohamed Houri — December 17, 2006 @ 10:33 am GMT Dec 17,2006 | Reply

  2. As of 10g release 2, it is no longer necessary to create either local partitions or primary keys on the “clone” table.

    Your example above can be simplified to:

    create table parent_clone as select * from parent where 1 = 0;

    alter table parent
    exchange partition p2000 with table parent_clone
    update indexes;

    alter table parent drop partition p2000;

    If you have global indexes, they must be maintained. If you have local indexes, you’d have to manually define each on the clone table to avoid an ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

    Comment by Adam Musch — May 29, 2007 @ 3:42 pm GMT May 29,2007 | Reply

  3. I’m on 9.2.0.8 – and I simply cant make it work. It always leaves me with the index on both master and detail to be rebuilt.

    And then I cant “use” the table while I do the operation anyway.

    This table is a 24-7 logging table. Is there a way to drop a partition (master & detail) in flight?

    Best regards and thanks for an informative website
    Mette

    Comment by Mette — May 6, 2008 @ 1:38 pm GMT May 6,2008 | Reply

  4. [...] Administrator’s Guide 10g Release 2 (10.2) Chapter 17 Managing Partitioned Tables and Indexes Drop parent partition post by Jonathan Lewis Effective Oracle by Design by Thomas Kyte Chapter 3-Architectural Decisions, Page 185 Comments [...]

    Pingback by Oracle Business Intelligence and Extraction, Transformation, Loading(ETL) Part 2 « H.Tonguç Yılmaz – Oracle Blog — July 2, 2009 @ 1:24 pm GMT Jul 2,2009 | Reply

  5. still not working this way. including 11g also not working… please exapalin how.

    Comment by bala — July 15, 2011 @ 8:14 pm GMT Jul 15,2011 | Reply

  6. I’m on 11.2.0.3 and it just won’t work, I still get “ORA-02266: unique/primary keys in table referenced by enabled foreign keys”; however,
    I now disable the constraint before starting and then “enable novalidate” when done, which is quick since it doesn’t validate the old existing data.
    For what I’m doing it should be fine. I wish I had 12c so I could use interval based reference partitioning

    Comment by tinky2jed — November 22, 2013 @ 10:00 pm GMT Nov 22,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,306 other followers