Home » SQL & PL/SQL » SQL & PL/SQL » update a big table (Oracle Database 9i)
update a big table [message #420301] Fri, 28 August 2009 04:24 Go to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Hello,
i wanna update a column in a table (make it null). but the table is very big, and if i write something like
update my_table set column_name=null

it takes very much time (probably hours)

How can i optimize that? Any workaround for this to reduce the time? how can i update first 10000 recordsm then make a commit, then again 10000 and commit, and so on.. (i think it's a solution but not sure)

Thanks

[Updated on: Fri, 28 August 2009 04:28]

Report message to a moderator

Re: update a big table [message #420303 is a reply to message #420301] Fri, 28 August 2009 04:30 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Fastest way is to recreate the table using CREATE TABLE AS SELECT (CTAS).
Re: update a big table [message #420304 is a reply to message #420303] Fri, 28 August 2009 04:33 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
i don't wanna to use CREATE TABLE AS SELECT .....
i wanna update and commit each 10000 rows selected.. how is it possible?
Re: update a big table [message #420306 is a reply to message #420304] Fri, 28 August 2009 04:36 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Roger22 wrote on Fri, 28 August 2009 10:33
i don't wanna to use CREATE TABLE AS SELECT .....
i wanna update and commit each 10000 rows selected.. how is it possible?

No, you asked how to optimize the process. And cookie gave you the most optimal solution. You then decide to add caveats after the fact. Best bet would be for you to re-explain what your requirements are including all of the methods that you don't want to use (along with valid reasons as to why not) That way, people won't have to waste their time posting solutkioons that you decided you don't want to use.
Re: update a big table [message #420307 is a reply to message #420304] Fri, 28 August 2009 04:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So you come here to ask how to make your stuff go faster. You get an answer, describing what probably is the only option to gain performance and you turn it down, because you want to use method X. (which is only going to slow things down compared to what you are doing already).
If you know what you want, just write what you need.
Re: update a big table [message #420308 is a reply to message #420307] Fri, 28 August 2009 04:39 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
ok , then i must issue
create table new_table as (SELECT * from my_table)

?
and then what about the update?
Re: update a big table [message #420310 is a reply to message #420308] Fri, 28 August 2009 04:45 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
who said you must issue
create table new_table as (SELECT * from my_table)

No you must issue
CREATE TABLE AS SELECT col1, col2, col3, NULL col4, col5
FROM yourtab
Re: update a big table [message #420313 is a reply to message #420310] Fri, 28 August 2009 04:53 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
create table cereri1 as (select id, NULL arhivata_oi, validata from cereri where rownum<100)
 
ORA-01723: zero-length columns are not allowed
Re: update a big table [message #420314 is a reply to message #420313] Fri, 28 August 2009 04:57 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Embarassed
In that case, I would simply drop and recreate the column. If you are cocerned about time, you could set the column to UNUSED and drop it during a quiet period.
Re: update a big table [message #420315 is a reply to message #420301] Fri, 28 August 2009 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use CAST to specify the datatype and size of the nulled out column.
Re: update a big table [message #420316 is a reply to message #420314] Fri, 28 August 2009 05:00 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
if i try to set the column as UNUSED i got ORA-00054 error (resource busy and acquire...)
Re: update a big table [message #420317 is a reply to message #420313] Fri, 28 August 2009 05:01 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You can either do following

create table cereri1 as (select id, cast(NULL as <<required datatype>>) arhivata_oi, validata from cereri ;



or

create table cereri1 as select id, arhivata_oi, validata from cereri where 1=0;

insert into cereri1 select id, NULL arhivata_oi, validata from cereri;



But, Why did you use rownum filter in your query?


Re: update a big table [message #420331 is a reply to message #420314] Fri, 28 August 2009 06:26 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
alter table cereri set unused column arhivata_oi;
alter table cereri drop unused columns checkpoint 250l;

i hope now will be faster.. is it a FULL TABLE ACCESS in this case or not?
Re: update a big table [message #420337 is a reply to message #420301] Fri, 28 August 2009 07:27 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
It really should be
Previous Topic: Comparing two or more cursors
Next Topic: utl_smtp Authentication POP3 before SMTP - ORA-29279
Goto Forum:
  


Current Time: Sat Dec 10 20:17:30 CST 2016

Total time taken to generate the page: 0.18062 seconds