关于MERGE操作的一些建议
Oracle可以将Update和Insert操作合并为一个MERGE操作。第一可以更加方便操作、简化逻辑，第二在Oracle的内部进行了效率的优化。
在下面的举例可以看出，如果使用PLSQL中的exception来判断是UPDATE还是INSERT，效率是及其低下的。而在SQL层级使用MERGE或者手动分两步进行INSERT|UPDATE，效率相差无几。使用MERGE的优势是逻辑简单，一般无特使要求均以使用MERGE函数为佳。而使用两步的优点是可以返回有多少记录是UPDATE多少记录是INSERT的，还可以精确反映在哪一步发生错误。所以后两种方法各有优势，第一种方法是需要极力避免的。
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还建议：除非是特殊情况，否则不要使用分段提交。因为这样会破坏事务的一致性，而且会影响整体的性能。
如果一定要使用批量提交(例如undo表空间太小且不可增加时)，加入以下代码即可(只能用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;