RE: Data load ideas

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 29 Apr 2004 16:07:58 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FC0D@irvmbxw02>

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_at_quest.com> wrote:
> > No, after the exchange the indexes are not left in an unusable
> state.
> > See example below.

=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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Apr 29 2004 - 18:04:24 CDT