Oracle update性能优化
发布时间:2020-12-12 16:36:37 所属栏目:百科 来源:网络整理
导读:当执行批量刷新数据时,以前我写过最好是写成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 i
当执行批量刷新数据时,以前我写过最好是写成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; 总结:可以看到,第二种写法比第一种写法少扫描一张表,逻辑读也少了很多。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |