以前只考虑mergeinto只是在特定场合下方便才使用的，今天才发现，mergeinto竟然会比update在更新数据时有这么大的改进。其实呢，mergeinto部分的update和update也没啥不同的，不同的地方在于使用mergeinto后执行计划变了。赶紧测试看看，但是看到下面的结果，我和我的小伙伴惊呆了~

测试数据：createtabletest1asselect*fromdba_objectswhererownum<=10000;--10000条记录createtabletest2asselect*fromdba_objects;--13438条记录

更新相同的数据，看看下面mergeinto和update相比性能上有何改进。测试1：updateSQL>altersystemflushshared_pool;

系统已更改。SQL>altersystemflushbuffer_cache;

系统已更改。SQL>setlinesize400pagesize400SQL>setautottraceSQL>settimingonSQL>updatetest1t12sett1.object_name=3(selectt2.object_name4fromtest2t25wheret2.object_id=t1.object_id);

已更新10000行。

已用时间:00:00:25.24

执行计划----------------------------------------------------------Planhashvalue:3883393169

----------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------|0|UPDATESTATEMENT||9606|741K|518K(2)|01:43:46||1|UPDATE|TEST1||||||2|TABLEACCESSFULL|TEST1|9606|741K|40(0)|00:00:01||*3|TABLEACCESSFULL|TEST2|167|13193|53(0)|00:00:01|----------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):---------------------------------------------------

3-filter("T2"."OBJECT_ID"=:B1)

Note------dynamicsamplingusedforthisstatement(level=4)

统计信息----------------------------------------------------------234recursivecalls10665dbblockgets335physicalreads1631056redosize685bytessentviaSQL*Nettoclient705bytesreceivedviaSQL*Netfromclient3SQL*Netroundtripsto/fromclient17sorts(memory)0sorts(disk)10000rowsprocessed测试2：mergeintoSQL>altersystemflushshared_pool;

系统已更改。

已用时间:00:00:00.33SQL>altersystemflushbuffer_cache;

系统已更改。

已用时间:00:00:00.11SQL>mergeintotest1t12usingtest2t23on(t1.object_id=t2.object_id)4whenmatchedthen5updatesett1.object_name=t2.object_name;

10000行已合并。

已用时间:00:00:01.14

执行计划----------------------------------------------------------Planhashvalue:818823782

--------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|--------------------------------------------------------------------------------------|0|MERGESTATEMENT||9607|1238K||373(1)|00:00:05||1|MERGE|TEST1|||||||2|VIEW||||||||*3|HASHJOIN||9607|3996K|2168K|373(1)|00:00:05||4|TABLEACCESSFULL|TEST1|9606|2054K||40(0)|00:00:01||5|TABLEACCESSFULL|TEST2|16669|3369K||53(0)|00:00:01|--------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):---------------------------------------------------

3-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note------dynamicsamplingusedforthisstatement(level=4)

统计信息----------------------------------------------------------359recursivecalls10265dbblockgets343physicalreads2725336redosize685bytessentviaSQL*Nettoclient698bytesreceivedviaSQL*Netfromclient3SQL*Netroundtripsto/fromclient23sorts(memory)0sorts(disk)10000rowsprocessed

具体看下面的结果：SQL>setautotoffSQL>update/*+gather_plan_statistics*/test1t12sett1.object_name=3(selectt2.object_name4fromtest2t25wheret2.object_id=t1.object_id);

已更新10000行。

已用时间:00:00:27.26SQL>select*fromtable(dbms_xplan.display_cursor(null,null,'iostats'));

PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_IDc0pc2fq4pj4zq,childnumber0-------------------------------------update/*+gather_plan_statistics*/test1t1sett1.object_name=(selectt2.object_namefromtest2t2wheret2.object_id=t1.object_id)

Planhashvalue:3883393169

--------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|--------------------------------------------------------------------------------------|0|UPDATESTATEMENT||1||0|00:00:27.26|1800K||1|UPDATE|TEST1|1||0|00:00:27.26|1800K||2|TABLEACCESSFULL|TEST1|1|9606|10000|00:00:00.04|134||*3|TABLEACCESSFULL|TEST2|167|10000|00:00:27.03|1800K|--------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):---------------------------------------------------

3-filter("T2"."OBJECT_ID"=:B1)

Note------dynamicsamplingusedforthisstatement(level=4)

已选择26行。

SQL>merge/*+gather_plan_statistics*/intotest1t12usingtest2t23on(t1.object_id=t2.object_id)4whenmatchedthen5updatesett1.object_name=t2.object_name;

10000行已合并。

已用时间:00:00:00.25SQL>select*fromtable(dbms_xplan.display_cursor(null,null,'iostats'));

PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_IDcg8wb3hrjx2bd,childnumber0-------------------------------------merge/*+gather_plan_statistics*/intotest1t1usingtest2t2on(t1.object_id=t2.object_id)whenmatchedthenupdatesett1.object_name=t2.object_name

Planhashvalue:818823782

-------------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|Reads|-------------------------------------------------------------------------------------------------|0|MERGESTATEMENT||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|HASHJOIN||1|9607|10000|00:00:00.05|314|0||4|TABLEACCESSFULL|TEST1|1|9606|10000|00:00:00.01|134|0||5|TABLEACCESSFULL|TEST2|1|16669|13438|00:00:00.01|180|0|-------------------------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):---------------------------------------------------

3-access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note------dynamicsamplingusedforthisstatement(level=4)

已选择28行。