RE: Data load ideas
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Apr 2004 16:07:58 -0700
In the case of enabled foreign keys that reference the table to be
exchanged - that's the "worst-case" scenario, you will need to create
triggers to emulate those foreign keys, drop the foreign keys, do the
exchange, re-establish the foreign keys, and drop the triggers. The
triggers have to handle ON DELETE CASCADE and ON DELETE SET NULL foreign
key constraints. (I have a proof of concept lying around somewhere if
you are interested.)
Enabled FKs in the tables to be exchanged that reference other tables -
create matching disabled fks on the exchange table, then when you're
ready to do the exchange, disable the fk on the original table, do the
exchange, change the fk on the "permanent" table to enable novalidate.
(Eventually of course you will want to drop the disabled foreign keys
that still remain on the exchange table).
As long as you build matching indexes on the exchange table, and do the
exchange "including indexes", then the indexes will remain usable after
the exchange partition. Unless you have global indexes on the
partitioned table but of course no one would do that. :)
-----Original Message-----
Paul Baumgartel
Thanks--but what happens in the presence of enabled foreign keys that
reference these PKs, or enabled FKs in the tables to be exchanged
that reference other tables? I am finding that I can't do the exchange
unless all FKs are disabled. Will perform further tests tomorrow and
submit results.
>=20
> --- Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx> wrote:
> > No, after the exchange the indexes are not left in an unusable
> state.
> > See example below.
=09
=09
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs =20
http://hotjobs.sweepstakes.yahoo.com/careermakeover=20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
