用merge into进行性能优化

用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)| 00:00:12 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

统计信息
----------------------------------------------------------
378 recursive calls
310584 db block gets
8547 consistent gets
3751 physical reads
76712320 redo size
678 bytes sent via SQL*Net to client
671 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;

提交完成。

更多 0