RE: Adding new column for large table with online redefinition
Date: Fri, 30 May 2014 20:12:50 -0400
Message-ID: <CAEidWqMmGOfztx9BLj_NkYLftz2V=FgO80QqM-d8TDXnhLW_9g_at_mail.gmail.com>
If this can be done offline you could consider doing a "create table as select" operation joining the two tables to create a new table and swap them. This would also help avoid any migrated rows the update might create.
I have used dbms_redef but not in this way so also can't answer your question but would be curious how it works out for you.
Either way you do it you will also want to consider column position. If this is going to be a commonly populated and/or searched column you might want this earlier in the table. My experience has been new columns are often used more frequently than many older columns.
Kenny
On May 30, 2014 7:04 PM, "Prabhu Krishnaswamy" <prabhu_adam_at_hotmail.com>
wrote:
> Hi Mike,
>
> The system is a bi-directional replication environment, hence, we written
> a block which opens a cursor and does the batch commit for every 10000
> rows. For merge we did the same with every 50000 rows. We found the
> execution plan for the cursor SQL,update and merge statement are going
> through the index scan and one of the top most wait event was enq:
> TM-contention. Hence, we have dropped all the un indexed FKs referring
> this table.
>
> Thanks
> Prabhu
>
> ------------------------------
> From: mcunningham_at_thedoctors.com
> To: prabhu_adam_at_hotmail.com; oracle-l_at_freelists.org
> Date: Fri, 30 May 2014 14:55:38 -0700
> Subject: RE: Adding new column for large table with online redefinition
>
>
> Hi Prabhu, in my opinion 60+ million rows does not warrant 70 or 58 hours
> for the update. I’m concerned at that amount of time and wonder if the
> update is being performed with full scans. I know this does not answer
> your question about online redefinition and I have not tried redef for
> column addition so I am not able to offer help in that area. However, my
> first thought would be to look at why this is taking so long. Can you
> offer any further info such as execution plan and waits that you are
> experiencing during the update/merge?
>
>
>
> *Michael Cunningham*
> *Senior Database Administrator*
> *The Doctors' Company*
> 707.226.0221 - desk
> 707.337.0184 - cell
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Prabhu Krishnaswamy
> *Sent:* Friday, May 30, 2014 12:07 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* Adding new column for large table with online redefinition
>
>
>
> Lists
>
>
>
> We are adding a new column for a real large table (60+ million rows) and
> will update the column value from another table based on a matching
> condition. With normal update and merge statement, its taking around 70 and
> 58 hours respectively. We are exploring an option using online
> redefinition method. Is it possible to using the SQL on the column mapping
> string.
>
>
>
> Any suggestions or insights will be much helpful for us.
>
>
>
> Thanks for you help.
>
>
>
> Thanks
>
> Prabhu
>
>
>
> *Confidentiality Notice*: This message and any attachments hereto may
> contain confidential and privileged communications or information and/or
> attorney client communications or work-product protected by law. The
> information contained herein is transmitted for the sole use of the
> intended recipient(s). If you are not the intended recipient or designated
> agent of the recipient of such information, you are hereby notified that
> any use, dissemination, copying or retention of this e-mail or the
> information contained herein is strictly prohibited and may subject you to
> penalties under federal and/or state law. If you received this e-mail in
> error, please notify the sender immediately and permanently delete this
> e-mail.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat May 31 2014 - 02:12:50 CEST