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; 总结：可以看到，第二种写法比第一种写法少扫描一张表，逻辑读也少了很多。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址