当执行批量刷新数据时，以前我写过最好是写成merge into，当然还有一种方式，如下：

SQL> create table test1 as select * from dba_objects where rownum <100;
SQL> create table test2 as select * from dba_objects where rownum <1000;
?SQL> create unique index ind_t1_object_id on test1(object_id);
?SQL> create unique index ind_t2_object_id on test2(object_id);
?SQL> exec dbms_stats.gather_table_stats(user,'test1');
?SQL> exec dbms_stats.gather_table_stats(user,'test2');
?SQL> set autotrace traceonly
?SQL> update test1 t1
? ? ? ? set t1.object_name = (select t2.object_name
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? from test2 t2
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? where t1.object_id = t2.object_id)
? ? ? where exists (select 1 from test2 t3 where t3.object_id = t1.object_id);--千万不能丢掉exists，否则很多匹配不上的记录object_name就为null。
?已更新99行。
?执行计划
----------------------------------------------------------
Plan hash value: 1549919212
?-------------------------------------------------------------------------------------------------
?| Id? | Operation? ? ? ? ? ? ? ? ? ? | Name? ? ? ? ? ? | Rows? | Bytes | Cost (%CPU)| Time? ? |
?-------------------------------------------------------------------------------------------------
?|? 0 | UPDATE STATEMENT? ? ? ? ? ? |? ? ? ? ? ? ? ? ? |? ? 99 |? 1584 |? 304? (33)| 00:00:04 |
?|? 1 |? UPDATE? ? ? ? ? ? ? ? ? ? ? | TEST1? ? ? ? ? ? |? ? ? |? ? ? |? ? ? ? ? ? |? ? ? ? ? |
?|*? 2 |? HASH JOIN SEMI? ? ? ? ? ? |? ? ? ? ? ? ? ? ? |? ? 99 |? 1584 |? ? 7? (15)| 00:00:01 |
?|? 3 |? ? TABLE ACCESS FULL? ? ? ? | TEST1? ? ? ? ? ? |? ? 99 |? 1188 |? ? 3? (0)| 00:00:01 |
?|? 4 |? ? INDEX FAST FULL SCAN? ? ? | IND_T2_OBJECT_ID |? 999 |? 3996 |? ? 3? (0)| 00:00:01 |
?|? 5 |? TABLE ACCESS BY INDEX ROWID| TEST2? ? ? ? ? ? |? ? 1 |? ? 20 |? ? 2? (0)| 00:00:01 |
?|*? 6 |? ? INDEX RANGE SCAN? ? ? ? ? | IND_T2_OBJECT_ID |? ? 1 |? ? ? |? ? 1? (0)| 00:00:01 |
?-------------------------------------------------------------------------------------------------
?Predicate Information (identified by operation id):
?---------------------------------------------------
? ? 2 - access("T3"."OBJECT_ID"="T1"."OBJECT_ID")
? ? 6 - access("T2"."OBJECT_ID"=:B1)
统计信息
----------------------------------------------------------
? ? ? ? ? 3? recursive calls
? ? ? ? 103? db block gets
? ? ? 217? consistent gets
? ? ? ? ? 0? physical reads
? ? ? 23656? redo size
? ? ? ? 559? bytes sent via SQL*Net to client
? ? ? ? 922? bytes received via SQL*Net from client
? ? ? ? ? 3? SQL*Net roundtrips to/from client
? ? ? ? ? 2? sorts (memory)
? ? ? ? ? 0? sorts (disk)
? ? ? ? ? 99? rows processed