Simple Update Takes Hours

[craving94509]

I have a simple update that seems to take a long time (6 hours). I am wondering if anyone can offer a way to speed it up?

350,000 rows with no null values in the source column. The source column is a text field that has values as long as 3900 characters. There are no indexes on the table.

UPDATE TABLE1 SET COLUMN1 = COLUMN2

Appreciate any help.

 

[karluk]

If I had to guess without any further information, I would speculate that your "table" may in reality is a view with a complicated join condition. That's one plausible explanation for an apparently straightforward operation taking an inordinately long time.

But of course there is no reason to guess. Practically the first thing you should do when faced with performance problems in your sql is to generate the execution plan for slow running statement. That will more than likely give us some clues as to what's happening with your update. Please do so and report your results here.

 

[sem]

One more guess: locking


Regards, Dima

http://seminihin.narod.ru

 

[sem]

BTW how what's your table average row length?
Actually your statement needs approximately 1 GB in rollback segment at least, so it's not so simple as it looks like .


Regards, Dima

http://seminihin.narod.ru

 

[fredericofonseca]

look at updating that table in batches - this post has some examples of how to do it

http://stackoverflow.com/questions/...acle-pl-sql-in-batches-to-avoid-running-out-o

(The CTAS example on that post needs to be considered carefully - CTAS will not create the new table with defaults and other settings from the source table)



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[taupirho]

What happens if you try to emulate your update with a CTAS

i.e

create table2 as select column2 as column1, column2 as column2, ... columnN as columnN
from table1

if this method is really quick, then do it and just drop table1 afterwards and then
rename table2 to table1


In order to understand recursion, you must first understand recursion.

 

[fredericofonseca]

taupirho

a table created with CTAS will not keep all the original table definitions, and on those missing defaults are one of the aspects - in order to use the CTAS as the method, one would have after that to apply all those definitions that are not "copied" as part of the CTAS process.

So it is not just a process of running the CTAS, drop and rename. There is more to it

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[taupirho]

The OP doesn't give much context to the issue so your objections to my answer may or may not matter. In any case bringing the new table up to be the same definition as the old table would be trivial.

Let's see what the OP has to say about it


In order to understand recursion, you must first understand recursion.