關(guān)于MERGE操作的一些建議
?
??? Oracle可以將Update和Insert操作合并為一個(gè)MERGE操作。第一可以更加方便操作、簡化邏輯，第二在Oracle的內(nèi)部進(jìn)行了效率的優(yōu)化。
?
??? 在下面的舉例可以看出，如果使用PLSQL中的exception來判斷是UPDATE還是INSERT，效率是及其低下的。而在SQL層級(jí)使用MERGE或者手動(dòng)分兩步進(jìn)行INSERT|UPDATE，效率相差無幾。使用MERGE的優(yōu)勢是邏輯簡單，一般無特使要求均以使用MERGE函數(shù)為佳。而使用兩步的優(yōu)點(diǎn)是可以返回有多少記錄是UPDATE多少記錄是INSERT的，還可以精確反映在哪一步發(fā)生錯(cuò)誤。所以后兩種方法各有優(yōu)勢，第一種方法是需要極力避免的。
?
SQL> create table t1 as select object_id, object_name from all_objects;
?
Table created.
?
SQL> alter table t1 add constraint t1_pk primary key(object_id);
?
Table altered.
?
SQL> create table t2 as select * from t1;
?
Table created.
?
SQL> alter table t2 add constraint t2_pk primary key(object_id);
?
Table altered.
?
SQL>
SQL> analyze table t1 compute statistics
? 2? for table
? 3? for all indexes
? 4? for all indexed columns;
?
Table analyzed.
?
SQL>
SQL> analyze table t2 compute statistics
? 2? for table
? 3? for all indexes
? 4? for all indexed columns;
?
Table analyzed.
?
SQL>
SQL> set timing on
SQL> declare
? 2????????? l_icnt number := 0;
? 3????????? l_ucnt number := 0;
? 4? begin
? 5????? for x in ( select * from t1 )
? 6????? loop
? 7????????? begin
? 8????????????? insert into t2 ( object_id, object_name ) values ( x.object_id, x.object_name );
? 9????????????????????????? l_icnt := l_icnt+1;
?10????????? exception
?11????????????? when dup_val_on_index then
?12????????????????? update t2 set object_name = x.object_name where object_id = x.object_id;
?13????????????????????????? l_ucnt := l_ucnt+1;
?14????????? end;
?15????? end loop;
?16????????? dbms_output.put_line( 'Inserted ' || l_icnt );
?17????????? dbms_output.put_line( 'Updated? ' || l_ucnt );
?18? end;
?19? /
Inserted 0
Updated? 29317
?
PL/SQL procedure successfully completed.
?
Elapsed: 00:01:04.07
SQL> rollback;
?
Rollback complete.
?
Elapsed: 00:00:00.86
SQL>
SQL> begin
? 2????? update ( select t1.object_name t1_oname, t2.object_name t2_oname
? 3???????????????? from t1, t2
? 4??????????????? where t1.object_id = t2.object_id )
? 5???????? set t1_oname = t2_oname;
? 6
? 7????????? dbms_output.put_line( 'Updated? ' || sql%rowcount );
? 8
? 9???????? insert into t1
?10????? select * from t2 where t2.object_id not in ( select object_id from t1 );
?11
?12????????? dbms_output.put_line( 'Inserted ' || sql%rowcount );
?13? end;
?14? /
Updated? 29317
Inserted 0
?
PL/SQL procedure successfully completed.
?
Elapsed: 00:00:02.44
SQL> rollback;
?
Rollback complete.
?
Elapsed: 00:00:01.07
SQL>
SQL> merge into t1
? 2? using t2
? 3? on ( t2.object_id = t1.object_id )
? 4? when matched then
? 5???? update set t1.object_name = t2.object_name
? 6? when not matched then
? 7????? insert (object_id, object_name) values( t2.object_id, t2.object_name);
?
29317 rows merged.
?
Elapsed: 00:00:02.87
SQL>
SQL> rollback;
?
Rollback complete.
?
Elapsed: 00:00:01.08
?
?
??? 另外，Tom還建議：除非是特殊情況，否則不要使用分段提交。因?yàn)檫@樣會(huì)破壞事務(wù)的一致性，而且會(huì)影響整體的性能。
??? 如果一定要使用批量提交(例如undo表空間太小且不可增加時(shí))，加入以下代碼即可(只能用cursor來update)
：
?
begin
??? ...
for xc1 in c loop
??? commit_point:=commit_point+1;
??? if commit_point > 10000 then
??? commit;
??? commit_point:=0;
??? end if;
??? begin
??????? ...
??? end;
end loop;
??? ...
end;
?
?
?