用merge into进行性能优化
有时候开发组有这么一个需求，一个表和它的备份表，把备份表中的某些字段替换到原表中，当数据量非常大的时候就很很慢，这个时候如果我们用merge into往往会提高几倍的性能，下面我们来做个实验：
SQL> drop table test1 purge;
表已删除。
SQL> drop table test2 purge;
表已删除。
SQL> create table test1 as select * from dba_objects;
表已创建。
SQL> alter table test1 nologging;
表已更改。
SQL> begin
2 for i in 1 .. 5 loop
3 insert /*+append*/
4 into test1
5 select * from dba_objects;
6 commit;
7 end loop;
8 end;
9 /
PL/SQL 过程已成功完成。
SQL> update test1 set object_id = rownum;
已更新303258行。
SQL> commit;
提交完成。
SQL> create table test2 as select * from test1;
表已创建。
SQL> select count(*) from test1;
COUNT(*)
----------
303258
SQL> select count(*) from test2;
COUNT(*)
----------
303258
SQL> create index ind_object_id1 on test1(object_id) nologging;
索引已创建。
SQL> create index ind_object_id2 on test2(object_id) nologging;
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'test1');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'test2');
PL/SQL 过程已成功完成。
SQL> set timing on
SQL> set autotrace traceonly
SQL> update test1 t1
2 set t1.object_type = (select object_type
3 from test2 t2
4 where t1.object_id = t2.object_id);
已更新303258行。
已用时间: 00: 00: 13.07
执行计划
----------------------------------------------------------
Plan hash value: 2560893763
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 303K| 4146K| 949 (2)| 00:00:12 |
| 1 | UPDATE | TEST1 | | | | |
| 2 | TABLE ACCESS FULL | TEST1 | 303K| 4146K| 949 (2)| 00:00:12 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 14 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_OBJECT_ID2 | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=:B1)
统计信息
----------------------------------------------------------
330 recursive calls
338515 db block gets
1250542 consistent gets
1 physical reads
107333692 redo size
673 bytes sent via SQL*Net to client
701 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
303258 rows processed
SQL> commit;
提交完成。
已用时间: 00: 00: 00.00
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_type = t2.object_type;
303258 行已合并。
已用时间: 00: 00: 03.87
执行计划
----------------------------------------------------------
Plan hash value: 520388833
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 303K| 5923K| | 4947 (2)| 00:01:00 |
| 1 | MERGE | TEST1 | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN | | 303K| 53M| 30M| 4947 (2)| 00:01:00 |
| 4 | TABLE ACCESS FULL| TEST2 | 303K| 26M| | 957 (3)| 00:00:12 |
| 5 | TABLE ACCESS FULL| TEST1 | 303K| 26M| | 957 (3)| 0
