溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
以前只考慮 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進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
