Oracle update性能优化

185次阅读

共计 3561 个字符，预计需要花费 9 分钟才能阅读完成。

当执行批量刷新数据时，以前我写过最好是写成 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;