以前只考虑 merge into 只是在特定场合下方便才使用的，今天才发现，merge into 竟然会比 update 在更新数据时有这么大的改进。
其实呢，merge into部分的update和update也没啥不同的，不同的地方在于使用merge into后执行计划变了。
赶紧测试看看，但是看到下面的结果，我和我的小伙伴惊呆了~
测试数据：
create table test1 as select * from dba_objects where rownum<=10000;--10000条记录
create table test2 as select * from dba_objects;--13438条记录
更新相同的数据，看看下面merge into和update相比性能上有何改进。
测试1：update
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> set linesize 400 pagesize 400
SQL> set autot trace
SQL> set timing on
SQL> update test1 t1
2 set t1.object_name =
3 (select t2.object_name
4 from test2 t2
5 where t2.object_id = t1.object_id);
已更新10000行。
已用时间: 00: 00: 25.24
执行计划
----------------------------------------------------------
Plan hash value: 3883393169
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 9606 | 741K| 518K (2)| 01:43:46 |
| 1 | UPDATE | TEST1 | | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 9606 | 741K| 40 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEST2 | 167 | 13193 | 53 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."OBJECT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
234 recursive calls
10665 db block gets
2282027 consistent gets
335 physical reads
1631056 redo size
685 bytes sent via SQL*Net to client
705 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
10000 rows processed
测试2：merge into
SQL> alter system flush shared_pool;
系统已更改。
已用时间: 00: 00: 00.33
SQL> alter system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 00.11
SQL> merge into test1 t1
2 using test2 t2
3 on (t1.object_id = t2.object_id)
4 when matched then
5 update set t1.object_name = t2.object_name;
10000 行已合并。
已用时间: 00: 00: 01.14
执行计划
----------------------------------------------------------
Plan hash value: 818823782
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 9607 | 1238K| | 373 (1)| 00:00:05 |
| 1 | MERGE | TEST1 | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN | | 9607 | 3996K| 2168K| 373 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| TEST1 | 9606 | 2054K| | 40 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TEST2 | 16669 | 3369K| | 53 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
359 recursive calls
10265 db block gets
964 consistent gets
343 physical reads
2725336 redo size
685 bytes sent via SQL*Net to client
698 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
23 sorts (memory)
0 sorts (disk)
10000 rows processed
测试结果对比：update和merge into 都更新1w条记录，update耗时25.24，逻辑读消耗2282027；merge into 耗时01.14s，消耗逻辑读964.相差太大了。
其实看着执行计划，这个结果也很容易理解：update采用的类似nested loop的方式，对更新的每一行，都会对查询的表扫描一次；merge into这里选择的是hash join，
则针对每张表都是做了一次 full table scan，对每张表都只是扫描一次。
具体看下面的结果：
SQL> set autot off
SQL> update /*+gather_plan_statistics*/test1 t1
2 set t1.object_name =
3 (select t2.object_name
4 from test2 t2
5 where t2.object_id = t1.object_id);
已更新10000行。
已用时间: 00: 00: 27.26
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID c0pc2fq4pj4zq, child number 0
-------------------------------------
update /*+gather_plan_statistics*/test1 t1 set t1.object_name =
(select t2.object_name from test2 t2 where
t2.object_id = t1.object_id)
Plan hash value: 3883393169
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:27.26 | 1800K|
| 1 | UPDATE | TEST1 | 1 | | 0 |00:00:27.26 | 1800K|
| 2 | TABLE ACCESS FULL| TEST1 | 1 | 9606 | 10000 |00:00:00.04 | 134 |
|* 3 | TABLE ACCESS FULL| TEST2 | 10000 | 167 | 10000 |00:00:27.03 | 1800K|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."OBJECT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement (level=4)
已选择26行。
SQL> merge /*+gather_plan_statistics*/into test1 t1
2 using test2 t2
3 on (t1.object_id = t2.object_id)
4 when matched then
5 update set t1.object_name = t2.object_name;
10000 行已合并。
已用时间: 00: 00: 00.25
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID cg8wb3hrjx2bd, child number 0
-------------------------------------
merge /*+gather_plan_statistics*/into test1 t1 using test2 t2 on
(t1.object_id = t2.object_id) when matched then update set
t1.object_name = t2.object_name
Plan hash value: 818823782
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.22 | 10568 | 1 |
| 1 | MERGE | TEST1 | 1 | | 0 |00:00:00.22 | 10568 | 1 |
| 2 | VIEW | | 1 | | 10000 |00:00:00.05 | 314 | 0 |
|* 3 | HASH JOIN | | 1 | 9607 | 10000 |00:00:00.05 | 314 | 0 |
| 4 | TABLE ACCESS FULL| TEST1 | 1 | 9606 | 10000 |00:00:00.01 | 134 | 0 |
| 5 | TABLE ACCESS FULL| TEST2 | 1 | 16669 | 13438 |00:00:00.01 | 180 | 0 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=4)
已选择28行。
