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:49:25 -0600
I understand all of this, but you can instantiate a Materialzed View (which
is the interim table) without locking the source table. You can use a direct
load insert as select to move the data, then you could then use the create
materialzed view using the using prebuilt table (I think thats the key) to
actually create the MView. I don't know if this is how Oracle does it, but I
am fairly certain that this operation would allow for an online redefinition
to occur. Of course, i've been wrong before, I could be wrong now! :-)
As an aside, I am quite aware of how online redefs work, I did write
Oracle9i New Features, which has a whole section on this beast. So, I know
how it works from the outside, I'm wondering how it works on the inside.
Thanks for your thoughts! :-)
Robert
-----Original Message-----
From: Mladen Gogala
To: oracle-l@xxxxxxxxxxxxx
Sent: 3/9/2004 1:14 PM
Subject: Re: Online Redefinition - FK on vs. FK off
On 03/09/2004 02:05:46 PM, Freeman Robert - IL wrote:
> 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, I beg to differ. DBMS_REDEFINITION works online, while the table
is
in use. The intermediate table becomes the new table after the procedure
ends.
While copying, the original table must be accessible and the rows
mustn't
be locked. The only way to actually do that is to store the data from
the original
table into an MV.
----------------------------------------------------------------
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:
