Oracle
发布时间：2022-05-17 发布网站：大佬教程 code.js-code.com
大佬教程收集整理的这篇文章主要介绍了Oracle update性能优化，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
@H_
673_0@
当执行批量刷新数据时，以前我写过最好是写成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;
总结：可以看到，第二种写法比第一种写法少扫描一张表，逻辑读也少了很多。
