RE: drop partition - with foreign keys enabled?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <granaman@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Jan 2005 11:17:52 -0800
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Don Granaman
Sent: mardi, 18. janvier 2005 02:59
To: oracle-l@xxxxxxxxxxxxx
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).
--
//www.freelists.org/webpage/oracle-l
Other related posts:
