OraFAQ Forum: SQL & PL/SQL » Column Addition and Update on Big table

Home » SQL & PL/SQL » SQL & PL/SQL » Column Addition and Update on Big table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Column Addition and Update on Big table [message #284461]

Thu, 29 November 2007 14:39

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

Report message to a moderator

Re: Column Addition and Update on Big table [message #284464 is a reply to message #284461]

Thu, 29 November 2007 14:52

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Column Addition and Update on Big table [message #284465 is a reply to message #284464]

Thu, 29 November 2007 14:57

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

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: Column Addition and Update on Big table [message #284484 is a reply to message #284465]

Thu, 29 November 2007 20:48

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 13 11:57:07 CST 2025