Oracle update性能优化
当执行批量刷新数据时，以前我写过最好是写成merge into，当然还有一种方式，如下：
SQL> create table test1 as select * from dba_objects where rownum <100;
SQL> create table test2 as select * from dba_objects where rownum <1000;
SQL> create unique index ind_t1_object_id on test1(object_id);
SQL> create unique index ind_t2_object_id on test2(object_id);
SQL> exec dbms_stats.gather_table_stats(user,'test1');
SQL> exec dbms_stats.gather_table_stats(user,'test2');
SQL> set autotrace traceonly
SQL> update test1 t1
set t1.object_name = (select t2.object_name
from test2 t2
where t1.object_id = t2.object_id)
where exists (select 1 from test2 t3 where t3.object_id = t1.object_id);--千万不能丢掉exists，否则很多匹配不上的记录object_name就为null。
已更新99行。
执行计划
----------------------------------------------------------
Plan hash value: 1549919212
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 99 | 1584 | 304 (33)| 00:00:04 |
| 1 | UPDATE | TEST1 | | | | |
|* 2 | HASH JOIN SEMI | | 99 | 1584 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST1 | 99 | 1188 | 3 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN | IND_T2_OBJECT_ID | 999 | 3996 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 20 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_T2_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."OBJECT_ID"="T1"."OBJECT_ID")
6 - access("T2"."OBJECT_ID"=:B1)
统计信息
----------------------------------------------------------
3 recursive calls
103 db block gets
217 consistent gets
0 physical reads
23656 redo size
559 bytes sent via SQL*Net to client
922 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99 rows processed
SQL> commit;
SQL> update (select t1.object_name, t2.object_name new_object_name
from test1 t1, test2 t2
where t1.object_id = t2.object_id)
set object_name = new_object_name;
已更新99行。
执行计划
----------------------------------------------------------
Plan hash value: 1124869545
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 99 | 3168 | 10 (10)| 00:00:01 |
| 1 | UPDATE | TEST1 | | | | |
|* 2 | HASH JOIN | | 99 | 3168 | 10 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 99 | 1188 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST2 | 999 | 19980 | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
统计信息
----------------------------------------------------------
3 recursive calls
103 db block gets
25 consistent gets
0 physical reads
23736 redo size
561 bytes sent via SQL*Net to client
858 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99 rows processed
SQL> commit;
总结：可以看到，第二种写法比第一种写法少扫描一张表，逻辑读也少了很多。