drop partition - with foreign keys enabled?

  • From: "Don Granaman" <granaman@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 18 Jan 2005 02:58:31 -0800

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 */
(Method 1) Disable the constraints, drop the partition, re-enable the

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 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 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).

Don Granaman
OraSaurus (...still...)


Other related posts: