Re: drop partition - with foreign keys enabled?

  • From: "Don Granaman" <granaman@xxxxxxx>
  • To: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 20 Jan 2005 22:14:22 -0800

http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm
Oracle9i Database Administrator's Guide
Release 2 (9.2)
Part Number A96521-01
Chapter 17 - About half way in, verbatim:
Dropping a Partition Containing Data and Referential Integrity Constraints
If a partition contains data and the table has referential integrity 
constraints, choose either of the following methods to drop the table 
partition. This table has a local index only, so it is not necessary to rebuild 
any indexes.

Method 1:

Disable the integrity constraints, issue the ALTER TABLE ... DROP PARTITION 
statement, then enable the integrity constraints:

ALTER TABLE sales
   DISABLE CONSTRAINT dname_sales1;
ALTER TABLE sales DROP PARTITTION dec98;
ALTER TABLE sales
   ENABLE CONSTRAINT dname_sales1;

This method is most appropriate for large tables where the partition being 
dropped contains a significant percentage of the total data in the table.

Method 2:

Issue the DELETE statement to delete all rows from the partition before you 
issue the ALTER TABLE ... DROP PARTITION statement. The DELETE statement 
enforces referential integrity constraints, and also fires triggers and 
generates redo and undo log.

DELETE FROM sales WHERE TRANSID < 10000;
ALTER TABLE sales DROP PARTITION dec94;

This method is most appropriate for small tables or for large tables when the 
partition being dropped contains a small percentage of the total data in the 
table.



I think I'll opt for method 3 - just drop the partition, without jumping 
through any flaming hoops.

-Don Granaman

----- Original Message ----- 
  From: Jacques Kilchoer 
  To: Don Granaman ; oracle-l@xxxxxxxxxxxxx 
  Sent: Thursday, January 20, 2005 10:32 AM
  Subject: RE: drop partition - with foreign keys enabled?


  Yes, the documentation is wrong. Where did it say this in the documentation? 
I couldn't find it.
  I can drop a partition from order_detail without disabling order_detail_fk1. 

  -----Original Message-----
  From: Don Granaman [mailto:granaman@xxxxxxx]
  Sent: mercredi, 19. janvier 2005 23:10
  To: Jacques Kilchoer; oracle-l@xxxxxxxxxxxxx
  Subject: Re: drop partition - with foreign keys enabled?

  [I replied to this last night, but it was rejected for over-quoting.]

  Perhaps I was not clear...

  The situation is, using your example, that the docs say that to drop (or
  exchange) a partition from the (referencing) ORDER_DETAIL table, one has to
  first disable the foreign key ORDER_DETAIL_FK1.  This is what seems odd.
  One would not need to disable this foreign key prior to truncating a
  non-partitioned ORDER_DETAIL table.  I suspect that this is simply a
  documentation bug.

  It does makes perfect sense that one would have to disabled the
  ORDER_DETAIL_FK1 constraint prior to dropping (or exchanging) a partition
  from the (referenced) ORDERS table - for the reason you mentioned.

  Don Granaman





--
//www.freelists.org/webpage/oracle-l

Other related posts: