RE: Online Redefinition - FK on vs. FK off
- From: Freeman Robert - IL <FREEMANR@xxxxxxxx>
- To: 'Mladen Gogala ' <mladen@xxxxxxxxxxxxxxx>,"'oracle-l@xxxxxxxxxxxxx '" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Mar 2004 13:05:46 -0600
In fact, for the reasons you mention, I didn't want the FK's on... I just
realized after the fact that they were, and I was looking at the cost and
was like WOW! Phenominal. However, this is during the initial population of
the interim table, which I would think would not be impacted by fast or full
refreshes of the mview. My guess is they copy the data into the table and
then create the MVIEW afterwards using the prebuilt table clause..... don't
you think?
Robert
-----Original Message-----
From: Mladen Gogala
To: oracle-l@xxxxxxxxxxxxx
Sent: 3/9/2004 12:55 PM
Subject: Re: Online Redefinition - FK on vs. FK off
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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
