Home » RDBMS Server » Performance Tuning » updation of 200millions rows (Oracle, 10.2.0.3, AIX 5.3 ML9)
updation of 200millions rows [message #389015] Fri, 27 February 2009 00:18 Go to next message
junaidsystems
Messages: 30
Registered: June 2006
Member
Hi,

I have added a new column in a table with 200 millions rows and now I want to update this with value 0

I issue the simple command:

update tbale_name set col=0

It is not able to update column even after 7 hours.

Any body have experience like this. I visited many groups and tried many other options such as cursors, etc but problem still there.

regards.
Re: updation of 200millions rows [message #389017 is a reply to message #389015] Fri, 27 February 2009 00:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
All other things being equal, 200 millions rows can be updated in minutes.


Re: updation of 200millions rows [message #389019 is a reply to message #389015] Fri, 27 February 2009 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing to do, you have to wait.
You can activate parallelism if you have resources that support it.
You can also (and above all) do it when no one else works.

Regards
Michel
Re: updation of 200millions rows [message #389032 is a reply to message #389019] Fri, 27 February 2009 01:34 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Probably you can try using ctas option.

CREATE TABLE new_table as select <do the update "here"> from old_table;


Can refer to the following link for more info

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330
Re: updation of 200millions rows [message #389041 is a reply to message #389032] Fri, 27 February 2009 01:49 Go to previous messageGo to next message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
Try using the default value while creating the column itself, since you have mentioned that you have added a column.
Re: updation of 200millions rows [message #389048 is a reply to message #389041] Fri, 27 February 2009 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rsoma wrote on Fri, 27 February 2009 08:49
Try using the default value while creating the column itself, since you have mentioned that you have added a column.

This does not change the overall time.
Adding a column is very low cost, this is adding the value that is expensive.

Regards
Michel

Re: updation of 200millions rows [message #389064 is a reply to message #389048] Fri, 27 February 2009 02:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do you have any On Update triggers on the table - if so, you might want to look at disabling them for this update, or rewriting them to exclude updates to this column.

Re: updation of 200millions rows [message #389082 is a reply to message #389015] Fri, 27 February 2009 04:00 Go to previous messageGo to next message
junaidsystems
Messages: 30
Registered: June 2006
Member

JRowbottom : The table does not have any trigger associate with it.

rsoma : Deafault value clause is also not working. However the new column addition took only a minute.


Re: updation of 200millions rows [message #389127 is a reply to message #389082] Fri, 27 February 2009 06:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How many rows does the table have?

Is an application using this table using this table while you're trying to do the update?
Re: updation of 200millions rows [message #389138 is a reply to message #389015] Fri, 27 February 2009 06:37 Go to previous messageGo to next message
junaidsystems
Messages: 30
Registered: June 2006
Member
JRowbottom:
- Around 200 million records
- I am using plsql developer and sqlplus

This is a production database.
Re: updation of 200millions rows [message #389142 is a reply to message #389015] Fri, 27 February 2009 07:03 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are users using the system while you're trying to do this update?
Re: updation of 200millions rows [message #389148 is a reply to message #389015] Fri, 27 February 2009 07:16 Go to previous messageGo to next message
junaidsystems
Messages: 30
Registered: June 2006
Member

No. All users are of line and it is not 24/7 .
Re: updation of 200millions rows [message #389163 is a reply to message #389015] Fri, 27 February 2009 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try CTAS as suggested before

[Updated on: Fri, 27 February 2009 08:09]

Report message to a moderator

Re: updation of 200millions rows [message #389165 is a reply to message #389148] Fri, 27 February 2009 08:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look at Dba_Blockers while you're doing this - see if there are any blocking locks.
Re: updation of 200millions rows [message #389735 is a reply to message #389165] Tue, 03 March 2009 07:27 Go to previous messageGo to next message
ahudspith
Messages: 26
Registered: January 2009
Location: Avoiding the tax man.
Junior Member
Is the column the last one in the table?

If it is then just drop it and recreate the column with a default value.

For gods sake do this in a test system first to see if there is sufficient gain to justify it first tho...
Re: updation of 200millions rows [message #389768 is a reply to message #389735] Tue, 03 March 2009 09:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Recreating the column with a default value will be no quicker than doing the update.
Here's a test you can run to demonstrate this:
create table test_127 (col_1  number);

insert into test_127 (select level from dual connect by level <= 1000000);

declare
  v_iter   pls_integer := 10;
  v_time   pls_integer;
  v_tot    pls_integer := 0;
begin
 
  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    execute immediate 'alter table test_127 add (col_2 number default 0)';
    v_tot := v_tot + (dbms_utility.get_time - v_time);

    execute immediate 'alter table test_127 drop column col_2';
  end loop;

  dbms_output.put_line('Test 1 '||to_char(v_tot));
  
  v_tot := 0
  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    execute immediate 'alter table test_127 add (col_2 number)';
    
    execute immediate 'update test_127 set col_2 = 0';
    v_tot := v_tot + (dbms_utility.get_time - v_time);

    execute immediate 'alter table test_127 drop column col_2';
  end loop;

  dbms_output.put_line('Test 2 '||to_char(v_tot));  
end;
/


I just ran this and got these results:
Test 1 150334
Test 2 142476

Actually, if the OP could run this, and tell us how long it takes, that might be informative.
Re: updation of 200millions rows [message #389876 is a reply to message #389768] Tue, 03 March 2009 20:51 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
useful info to demonstate timing for both apporach,

by the way, what is OP?
Re: updation of 200millions rows [message #389997 is a reply to message #389876] Wed, 04 March 2009 08:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
OP = Original Post/Poster
Previous Topic: Difference between cost and %CPU?
Next Topic: Hash Joins and Memory Requirements
Goto Forum:
  


Current Time: Sat Dec 10 12:40:22 CST 2016

Total time taken to generate the page: 0.05570 seconds