merge into 和 update 的效率对比

merge into 和 update 的效率对比

以前只考虑 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：updateSQL>  alter system flush shared_pool;

系统已更改。SQL>  alter system flush buffer_cache;

系统已更改。SQL> set linesize 400 pagesize 400SQL> set autot traceSQL> set timing onSQL> 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 intoSQL>  alter system flush shared_pool;

系统已更改。

已用时间:  00: 00: 00.33SQL>  alter system flush buffer_cache;

系统已更改。

已用时间:  00: 00: 00.11SQL> 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 offSQL> 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.26SQL> 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          wheret2.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.25SQL> 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 sett1.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行。