您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
以前只考虑 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 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 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
具体看下面的结果： 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 | 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行。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。