merge与update效率比较系列1

最新推荐文章于 2024-01-15 11:20:46 发布

jgmydsai

最新推荐文章于 2024-01-15 11:20:46 发布

阅读量2.1k

点赞数

本文链接：https://blog.csdn.net/jgmydsai/article/details/10984211

版权

建立测试用表

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 size
	685  bytes sent via SQL*Net to client
	686  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 size
	685  bytes sent via SQL*Net to client
	675  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
      72780  rows processed

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