\u5f53\u6267\u884c\u6279\u91cf\u5237\u65b0\u6570\u636e\u65f6\uff0c\u4ee5\u524d\u6211\u5199\u8fc7\u6700\u597d\u662f\u5199\u6210merge into\uff0c\u5f53\u7136\u8fd8\u6709\u4e00\u79cd\u65b9\u5f0f\uff0c\u5982\u4e0b\uff1a<\/p>
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);--\u5343\u4e07\u4e0d\u80fd\u4e22\u6389exists\uff0c\u5426\u5219\u5f88\u591a\u5339\u914d\u4e0d\u4e0a\u7684\u8bb0\u5f55object_name\u5c31\u4e3anull\u3002<\/span>
?\u5df2\u66f4\u65b099\u884c\u3002
?\u6267\u884c\u8ba1\u5212
----------------------------------------------------------
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)
\u7edf\u8ba1\u4fe1\u606f
----------------------------------------------------------
? ? ? ? ? 3? recursive calls
? ? ? ? 103? db block gets
? ? ? 217? consistent gets<\/span>
? ? ? ? ? 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<\/p>
?SQL> commit;
?SQL> update (select t1.object_name, t2.object_name new_object_name
? ? ? ? ? ? ? from test1 t1, test2 t2
? ? ? ? ? ? ? where t1.object_id = t2.object_id)
? ? set object_name = new_object_name;
\u5df2\u66f4\u65b099\u884c\u3002
?\u6267\u884c\u8ba1\u5212
----------------------------------------------------------
Plan hash value: 1124869545
?-----------------------------------------------------------------------------
?| Id? | Operation? ? ? ? ? | Name? | Rows? | Bytes | Cost (%CPU)| Time? ? |
?-----------------------------------------------------------------------------
?|? 0 | UPDATE STATEMENT? ? |? ? ? |? ? 99 |? 3168 |? ? 10? (10)| 00:00:01 |
?|? 1 |? UPDATE? ? ? ? ? ? | TEST1 |? ? ? |? ? ? |? ? ? ? ? ? |? ? ? ? ? |
?|*? 2 |? HASH JOIN? ? ? ? |? ? ? |? ? 99 |? 3168 |? ? 10? (10)| 00:00:01 |
?|? 3 |? ? TABLE ACCESS FULL| TEST1 |? ? 99 |? 1188 |? ? 3? (0)| 00:00:01 |
?|? 4 |? ? TABLE ACCESS FULL| TEST2 |? 999 | 19980 |? ? 6? (0)| 00:00:01 |
?-----------------------------------------------------------------------------
?Predicate Information (identified by operation id):
?---------------------------------------------------
?","orderid":"0","title":"Oracle update\u6027\u80fd\u4f18\u5316(\u4e00)","smalltitle":"","mid":"0","fname":"\u6570\u636e\u5e93\u7f16\u7a0b","special_id":"0","bak_id":"0","info":"0","hits":"420","pages":"2","comments":"0","posttime":"2017-01-24 08:15:23","list":"1485216923","username":"admin","author":"","copyfrom":"","copyfromurl":"","titlecolor":"","fonttype":"0","titleicon":"0","picurl":"https:\/\/www.cppentry.com\/upload_files\/","ispic":"0","yz":"1","yzer":"","yztime":"0","levels":"0","levelstime":"0","keywords":"Oracle<\/A> update<\/A> \u6027\u80fd<\/A> \u4f18\u5316<\/A>","jumpurl":"","iframeurl":"","style":"","template":"a:3:{s:4:\"head\";s:0:\"\";s:4:\"foot\";s:0:\"\";s:8:\"bencandy\";s:0:\"\";}","target":"0","ip":"14.17.22.31","lastfid":"0","money":"0","buyuser":"","passwd":"","allowdown":"","allowview":"","editer":"","edittime":"0","begintime":"0","endtime":"0","description":"Oracle update\u6027\u80fd\u4f18\u5316","lastview":"1713834153","digg_num":"6574","digg_time":"0","forbidcomment":"0","ifvote":"0","heart":"","htmlname":"","city_id":"0"},"page":"1"}