Q
Problem solve Get help with specific problems with your technologies, process and projects.

Best way to update column of table with 5 million records

I have a table t1 with 5 million records. I have to update col2 of the table t1 as follows:

update t1 set col2=1 where col2=a;
update t1 set col2=2 where col2=b;
update t1 set col2=3 where col2=c;
update t1 set col2=4 where col2=d;
update t1 set col2=5 where col2=e;

and so on for 400 more values. Please let me know if there is a better way of updating the col2 of t1 instead of firing so many update statements. Can I do it with a single update? If so, then how and how will it affect performance?

Please note that 1,2,3 and a,b,c,.. can be any var cahr values. It would also be great if you can send the approximate performance numbers expected. would it be 1 min, 10 min, 1 hour etc for the above scenario. At present there is no index, Would indexing help in the update?

You can use a DECODE statement:

update t1 set col2=decode(col2,a,1,b,2....)

or a CASE statement:

update t1 set col2=CASE when col2= a then 1 when col2=b then 2...end;

As for performance, I can't estimate that; it all depends on your hardware, database layout, how many indexes are on the table, et cetera.


This was last published in October 2004

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close