Long running Update SQL

Not the first time that I have done such thing(s) but this time I decided to write it here.

There was this update statement which was written quite efficiently to update a table rows by matching with another table entries. The other table will have the original rowid of the main table that's being updated (basically need to modify the main table column type so had to create a temp table with all the records for those columns and stored the rowid also to make the later update faster).

So, when the table ddl operation is done, the update statement triggers and even with the rowid search criteria the update was taking more than one hour (killed the process after an hour).

So, made that as a pl/sql block by updating a single row at a time in a loop and it finishes in less than a minute.

So, pl/sql block to update a table is not bad as every body thinks. Of-course its like a small code rather than a single SQL but its sure very efficient as the single update statement has to do all searching, matching and updating in the memory where as the pl/sql block don't have to deal all of the records at once.

update sql:

update table1 a

set (a.col1, a.col2) = (select b.ncol1, b.ncol2

from temp_table1 b

where a.rowid = b.org_rowid)

where exists (select 1 from temp_table1 where a.rowid = org_rowid);

pl/sql Block:

begin

for i in (select org_rowid, ncol1, ncol2 from temp_table1) loop

update table1

set col1 = i.ncol1, col2 = i.ncol2

where rowid = i.org_rowid;

end loop;

end;

Hope this might help to some1.

I really appreciate if there is any better of handling this.