merge与update效率比较系列1

建立测试用表

CREATE TABLE test1 AS SELECT * FROM dba_objects;CREATE TABLE test2 AS SELECT * FROM Dba_Objects;ALTER TABLE test2 ADD constraints pk_test2 PRIMARY KEY (object_id);ALTER TABLE test1 ADD constraints pk_test1 PRIMARY KEY (object_id);

update计划如下

update test1 a set a.owner='SYS' where exists(select /*+ hash_sj*/ null from test2 b where b.object_id = a.object_id);72780 rows updated.Execution Plan----------------------------------------------------------Plan hash value: 32550816-------------------------------------------------------------------------------------------| Id  | Operation       | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |-------------------------------------------------------------------------------------------|   0 | UPDATE STATEMENT       |  | 75355 |  3164K|  |   587   (2)| 00:00:08 ||   1 |  UPDATE        | TEST1  |  |  |  |       |  ||*  2 |   HASH JOIN RIGHT SEMI |  | 75355 |  3164K|  1840K|   587   (2)| 00:00:08 ||   3 |    INDEX FAST FULL SCAN| PK_TEST2 | 75355 |   956K|  |    47   (3)| 00:00:01 ||   4 |    TABLE ACCESS FULL   | TEST1  | 77543 |  2271K|  |   292   (2)| 00:00:04 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("B"."OBJECT_ID"="A"."OBJECT_ID")Note-----   - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 78  recursive calls      74517  db block gets       1375  consistent gets  0  physical reads   18016816  redo size685  bytes sent via SQL*Net to client686  bytes received via SQL*Net from client  3  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk)      72780  rows processed

merge计划如下

merge into test1 a using test2 b on (b.object_id = a.object_id) when matched then update set a.owner='sys';Execution Plan----------------------------------------------------------Plan hash value: 520388833--------------------------------------------------------------------------------------| Id  | Operation     | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |--------------------------------------------------------------------------------------|   0 | MERGE STATEMENT      |     | 75356 |1251K|     |2222   (1)| 00:00:27 ||   1 |  MERGE     | TEST1 |     |     |     |  |     ||   2 |   VIEW     |     |     |     |     |  |     ||*  3 |    HASH JOIN     |     | 75356 |  30M|  15M|2222   (1)| 00:00:27 ||   4 |     TABLE ACCESS FULL| TEST2 | 75355 |  14M|     | 295   (2)| 00:00:04 ||   5 |     TABLE ACCESS FULL| TEST1 | 77543 |  16M|     | 294   (2)| 00:00:04 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("B"."OBJECT_ID"="A"."OBJECT_ID")Note-----   - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 71  recursive calls      74533  db block gets       2117  consistent gets  0  physical reads   18016788  redo size685  bytes sent via SQL*Net to client675  bytes received via SQL*Net from client  3  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk)      72780  rows processed

被更新字段内容与其它表无关联时，未发现merge有优势