Robert, DBMS_REDEFINITION (I suppose that is what you are using) uses materialized views.
If you don't have FK's, view definition is, essentially, "select * from table" and is
fast refreshable. With FK's, you have several tables involved, and MV is not fast
refreshable and oracle must invoke full refreshes. That takes a lot more time and space.
A lot more. The other question is: why would you like to have FK's turned on? Data in the
table has already been checked against the parent tables, so you know it is correct.
You can add FK's later and save yourself and your instance some trouble.
On 03/09/2004 01:29:16 PM, Freeman Robert - IL wrote:
> An interesting issue has shown up in some redefinitions I'm doing in 9i
> using the Online Redefinition features of Oracle. I'm wondering if anyone
> has any insight into this.
>
> I'm redefining a very LARGE LARGE table. This table has a number of FK
> constraints to other tables. If I redefine the table, and create the FK's on
> the interim table and leave then active, the redefinition process consumes
> some 18GB of undo. However, if I disable the constraints, the process
> consumes almost NO undo, and completes successfully.
>
> While I expected the constraints would result in a great deal of recursive
> SQL which would slow the process down, I'm shocked at the amount of undo
> that is being generated with then enabled as opposed to when they are off.
>
> Anyone have any thoughts on why this might be?
>
> Robert
> ----------------------------------------------------------------
> 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 Tue Mar 09 2004 - 12:54:37 CST
