RE: drop partition - with foreign keys enabled?

I don't understand your example too well. Let me rephrase it perhaps.

Let me take the tables ORDERS and ORDER_DETAIL with the following constraints:
ORDERS : primary key ORDERS_PK (order_id) ORDER_DETAIL : primary key ORDER_DETAIL_PK (order_id, item_id)

               foreign key ORDER_DETAIL_FK1 (order_id) references ORDERS
(order_id)

If ORDERS is a partitioned table, I cannot drop a partition from ORDERS because of the FK constraint ORDER_DETAIL_FK1 (ORA-02266). If I were able to do the drop partition, Oracle would have to read all of the ORDER_DETAIL table to make sure that I am not deleting values that are present in the child table, and the Oracle programmers didn't bother coding that functionality. To drop a partition (or exchange partition) from a parent table you will need to disable the foreign key constraint.

If ORDER_DETAIL is a partitioned table, I can drop a partition from ORDER_DETAIL. Any global indexes on ORDER_DETAIL will become invalid and need to be rebuilt. For more efficiency, I would avoid building global indexes.

This is the way it has always worked.

I'm not sure what you think is odd.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Granaman Sent: mardi, 18. janvier 2005 02:59
To: oracle-l_at_freelists.org
Subject: drop partition - with foreign keys enabled?

I'm rather new to some of this partition maintenance stuff, but this strikes
me as odd. Perhaps someone can shed some light...

When dropping a partition with referential integrity constraints, the Oracle9i Database Administrator's Guide for 9.2 says to either:

(Method 2) Delete the rows in the partition first /* not an option */
or
(Method 1) Disable the constraints, drop the partition, re-enable the
constraints

This seems odd. My situation, and the example in the docs, is a table with
referencing constraints only. The partitioned table is not referenced by
any foreign keys. I can see why attempting to drop a partition from a referenced table would be an issue, but don't know why this would be. So,
as an experiment, I dropped two different partitions from such a partitioned
table (with about 50 million rows, 10 million in each partition) in my 9.2.0.6 test database today. The first I did as suggested - disable all foreign keys, drop partition (with "update global indexes"), then "enable
novalidate" all foreign keys, followed by "enable validate" for each. Then
I did the same for another partition, but did not disable/re-enable any foreign keys. All foreign keys were enabled and validated and I simply dropped the partition (again, with "update global indexes"). In both cases,
inserts into the most recent "monthly" partition appeared to have no notable
problems. The latter was much faster and all constraints and indexes appear
to be in a valid state afterward. [If it makes any difference, each "monthly" date range partition is list subpartitioned about ten ways - by
CLIENT_ID, which does have a foreign key referencing the CLIENT table.]

What am I missing here? It doesn't make sense to me why these foreign keys
should have to be disabled prior to "alter table ... drop partition" - and
it seems to make no difference (other than the multi-hour performance hit to
re-validate them). However, I'm a little hesitant to try this in production - running 9.2.0.4 RAC and with over a billion rows split between
six partitions and sixty subpartitions.

I'm also looking for the most efficient way to drop the oldest partition while allowing inserts into the current "monthly" partition (no other DML -
anywhere) and then reclaim the "dropped" index space in two global indexes.
One has the primary key as the "leading column" and the other includes other
non-recurring values. All I can see is "alter table ... drop partition...
update global indexes", then rebuilding the two global indexes online
(which

requires about 50 GB of free index space), but it feels like a Rube Goldberg
deal (i.e. elaborate contraption to perform a simple task).

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