RE: Adding new column for large table with online redefinition
Date: Mon, 2 Jun 2014 16:47:44 -0400
Message-ID: <CAEidWqPH8j=RSqS2aj083beZtX9vJUJ2ReFs0s8eH58i+EfuHA_at_mail.gmail.com>
Hijacking a bit. A member pinged me privately asking about column position since I mentioned it. I responded with the following but then thought a larger audience might benefit or have additional information to share.
Two things come to mind, storage and cpu.
Null columns up to the last populated column in a row are stored as a null byte. If you have a table with 100 columns and all columns are null except for the 100th column then you have 99 null bytes per row. If you placed that column in the 1st position then you would avoid the 99 bytes per row of wasted space. Try adding a column with a default value to the end of a mostly null table and watch it grow. Tricks around that in 12c.
As for cpu, columns are null terminated. Oracle has to parse all columns up to the last column used in the query, either filter criteria or part of the result. The filter does not apply if it's filtered in a index. Obviously this parsing takes cpu. If you have to parse 100 columns per row in order to filter on column 100 that's going to take a fair bit more cpu than if that column was in position 1.
Kenny
On May 30, 2014 8:12 PM, "Kenny Payton" <k3nnyp_at_gmail.com> wrote:
> 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 Mon Jun 02 2014 - 22:47:44 CEST