From oracle-l-bounce@freelists.org Tue Mar 9 13:56:19 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i29JuIg13444 for ; Tue, 9 Mar 2004 13:56:18 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i29JuHo13431 for ; Tue, 9 Mar 2004 13:56:17 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7E9B7395979; Tue, 9 Mar 2004 14:46:38 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Mar 2004 14:45:21 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from tuscil_ex1.tuscil.com (exchange.tusc.com [65.174.243.31]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A3F56394DCD for ; Tue, 9 Mar 2004 14:44:42 -0500 (EST) Received: by tuscil_ex1 with Internet Mail Service (5.5.2657.72) id ; Tue, 9 Mar 2004 13:49:25 -0600 Message-ID: From: Freeman Robert - IL To: "'Mladen Gogala '" , "'oracle-l@freelists.org '" Subject: RE: Online Redefinition - FK on vs. FK off Date: Tue, 9 Mar 2004 13:49:25 -0600 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain; charset="iso-8859-1" X-archive-position: 212 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: FREEMANR@tusc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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@freelists.org 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@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@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 -----------------------------------------------------------------