http://tkyte.blogspot.com/2006/10/slow-by-slow.html
In there Tom says:
* You should do it in a single SQL statement if at all possible.
* If you cannot do it in a single SQL Statement, then do it in PL/SQL.

I was a true beliver of that until this case. We have a huge table, and we need to update it with values from another table.  

Let me give you an example
create table test (id number,col1 varchar2(20)) pctfree 99;
(this is only for creating a huge table with lots of columns)
declare
v_number number :=1;
begin
loop
insert into test values (v_number, 'a');
v_number := v_number+1;
exit when v_number > 1000000;
end loop;
commit;
end;

create table test1 as select * from test where id < 400000;
update test1 set col1='b';
commit;

create index test_ind on test(id);
create index test1_ind on test1(id);
analyze table test compute statistics;
analyze table test1 compute statistics;
SQL> update test
     set col1=(select col1 from test1
     where test.id=test1.id )
     where exists
     (select 1 from test1
     where test.id=test1.id);
399999 rows updated.
Elapsed: 00:04:15.01
SQL> declare
  2  v_count number;
  3  cursor test_curs is
  4  select test.id,test.col1 from test,test1
  5  where test.id=test1.id;
  6  begin
  7  for test_cur in test_curs
  8  loop
  9  update test
 10  set col1=test_cur.col1
 11  where id=test_cur.id;
 12  end loop;
 13  commit;
 14  end;
 15  /
PL/SQL procedure successfully completed.
Elapsed: 00:04:30.82

Here the difference is not huge yet, but in our case, single SQL will take an hour to finish, while PL/SQL only take 5 min.  The bigger the table test, the huger the difference. 

And the reason is simple, as there will be 40% of rows needs to be updated, Oracle will pick full table scan of table test(a huge table).  While in PL/SQL, "UPDATE TEST SET COL1=:B2 WHERE ID=:B1" will be able to use index.

I would like to see your comments.

Thanks very much as usual for your wonderful support.

Charlie

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 18 08:26:44 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


insert into test ( ID, COL1 )
select level as ID,  'a' as COL1
from dual
connect by level <= 1000000;
Elapsed: 00:06:41.41
SQL> create table test1 as select * from test where id < 400000;
Elapsed: 00:07:21.68
SQL> update test1 set col1='b';
Elapsed: 00:00:03.11
SQL> commit;
Elapsed: 00:00:00.03
SQL> create index test_ind on test(id);
Elapsed: 00:03:00.02
SQL> create index test1_ind on test1(id);
Elapsed: 00:00:02.47
SQL> analyze table test compute statistics for all columns for all indexes;
Elapsed: 00:01:07.56
SQL> analyze table test1 compute statistics for all columns for all indexes;
Elapsed: 00:00:05.74
SQL> set autot on exp;
SQL> update test
       set col1=(select col1 from test1
       where test.id=test1.id )
       where exists
       (select 1 from test1
       where test.id=test1.id);
Elapsed: 00:00:17.01

<<edited!>>
--------------------------------------------------
| Id  | Operation                    | Name      | 
--------------------------------------------------
|   5 |   TABLE ACCESS BY INDEX ROWID| TEST1     |
|*  6 |    INDEX RANGE SCAN          | TEST1_IND |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST"."ID"="TEST1"."ID")
   6 - access("TEST1"."ID"=:B1)

Note
-----
   - dynamic sampling used for this statement

SQL> rollback;
Elapsed: 00:00:08.92
SQL> ed
Wrote file afiedt.buf

   declare
     v_count number;
     cursor test_curs is
     select test.id,test.col1 from test,test1
     where test.id=test1.id;
   begin
     for test_cur in test_curs
      loop
      update test
      set col1=test_cur.col1
      where id=test_cur.id;
    end loop;
    --commit;
  end;
SQL> /
Elapsed: 00:00:28.55