Home » SQL & PL/SQL » SQL & PL/SQL » Column Addition and Update on Big table
Column Addition and Update on Big table [message #284461] Thu, 29 November 2007 14:39 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
hi,
We have to change a column with datatype of NUMBER(4) to NUMBER(6). So we are doing the following so that the current data is ported over:
ALTER TABLE ABC
MODIFY
(
    BEG_TIME number(6),
    END_TIME number(6)
);

followed by:

UPDATE ABC
    SET BEG_TIME = BEG_TIME * 100,
        END_TIME = END_TIME * 100;


The second statement would add "00" at the end of the previous four values. However, this table in production has 100 million rows and might take a long time to execute the above statement. Is there a better way to do this?


Thanks
Re: Column Addition and Update on Big table [message #284464 is a reply to message #284461] Thu, 29 November 2007 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no other way.

You don't add "00" at the end of previous values, you multiply these values by 100.
Adding string at the end of a number is meaningless.

Regards
Michel
Re: Column Addition and Update on Big table [message #284465 is a reply to message #284464] Thu, 29 November 2007 14:57 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Michel for Quick response,

it's not a string, Yes we are multiplying with 100.
I was thinking if CTAS will help, And this is a Partitioned Table.
Will update if i try with CTAS.

Thanks

[Updated on: Thu, 29 November 2007 15:10]

Report message to a moderator

Re: Column Addition and Update on Big table [message #284482 is a reply to message #284461] Thu, 29 November 2007 20:47 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
this is a common problem. You will be much better off in almost every case by simply recreating that table using

create table new_abc
nologging
as
select cast(beg_time*100 as number(6)) beg_time,cast(end_time*100 as number(6)) end_time
from abc
/


You should be able to achieve 1 million rows per minute without any effort at all. Then rebuild indexes etc.

Ask your DBA for assistance and understanding of logging practicies and need to backup once you are done.

Your milage may vary, as updates are affected to some degree by row width, and existence of indexes etc. But it can easily take an order of magnitude longer to do an in place update. But, since you have indicated your willingness to try both methods, please do so. There is nothing like actual experience to drive a point home.

Good luck, Kevin
Re: Column Addition and Update on Big table [message #284484 is a reply to message #284465] Thu, 29 November 2007 20:48 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Try this link
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330

HTH.
Regards,
Rhani

[Updated on: Fri, 30 November 2007 00:22] by Moderator

Report message to a moderator

Previous Topic: Calling procedure from application that only supports SQL
Next Topic: Regarding Aggregate conditions ..
Goto Forum:
  


Current Time: Tue Dec 06 14:12:54 CST 2016

Total time taken to generate the page: 0.09430 seconds