我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
Oracle笔记：测试update语句关联表扫描的次数
下面是测试一下update语句执行时，与更新表关联的表被扫描的次数，也是为什么我们常常将update语句，更改为merge into或者是pl/sql的方式来实现
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
1，数据库版本
www.htz.pw > select * from v$version;BANNER——————————————————————————–Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit ProductionPL/SQL Release 11.2.0.4.0 – ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 – ProductionNLSRTL Version 11.2.0.4.0 – Production
2，创建测试表
www.htz.pw > insert into scott.htz select * from scott.htz;690192 rows created.www.htz.pw > commit;Commit complete.www.htz.pw > select count(*) from scott.htz;COUNT(*)———-1380384www.htz.pw > update scott.htz1 set owner=’HTZ’;86275 rows updated.www.htz.pw > commit;Commit complete.www.htz.pw > create index scott.ind_htz2_object_id on scott.htz1(object_id,object_type,owner);Index created.
3，update更新表
www.htz.pw > update /*+ gather_plan_statistics */scott.htz a set (a.object_type,a.owner) = (select object_type,owner from scott.htz1 b where a.object_id=b.object_id);1380384 rows updated.Elapsed: 00:00:29.40Execution Plan———————————————————-Plan hash value: 932534721—————————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————————–| 0 | UPDATE STATEMENT | | 1442K| 56M| 11M (25)| 38:28:25 || 1 | UPDATE | HTZ | | | | || 2 | TABLE ACCESS FULL| HTZ | 1442K| 56M| 5416 (1)| 00:01:05 ||* 3 | INDEX RANGE SCAN | IND_HTZ2_OBJECT_ID | 886 | 36326 | 3 (0)| 00:00:01 |—————————————————————————————–Predicate Information (identified by operation id):—————————————————3 – access("B"."OBJECT_ID"=:B1)Note—–– dynamic sampling used for this statement (level=2)Statistics———————————————————-270 recursive calls1419046 db block gets1631433 consistent gets4781 physical reads382985292 redo size846 bytes sent via SQL*Net to client913 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1380384 rows processed
这里看到逻辑读是相当的高
下面查看一下htz1被扫描的次数
www.htz.pw > @find_sqlwww.htz.pw > set echo offEnter value for sql_text: gather_plan_statisticsEnter value for sql_id:SQL_ID CHILD HASH_VALUE PLAN_HASH EXECS ETIME AVG_ETIME USERNAME—————— —— ———- ———- ———- ————- ————- ————-SQLTEXT———————————————————————————————————————————————————————————————-219bgfbrqx1ck 0 4016997778 932534721 1 36.51 36.51 SYSupdate /*+ gather_plan_statistics */scott.htz a set (a.object_type,a.owner) = (select object_type,owner from scott.htz1 b where a.object_id=b.object_id)www.htz.pw > @plan_by_last.sqlEnter value for sqlid: 219bgfbrqx1ckPLAN_TABLE_OUTPUT——————————————————————————————————————————————————————————————————–SQL_ID 219bgfbrqx1ck, child number 0————————————-update /*+ gather_plan_statistics */scott.htz a set(a.object_type,a.owner) = (select object_type,owner from scott.htz1 bwhere a.object_id=b.object_id)Plan hash value: 932534721————————————————————————————————————| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |————————————————————————————————————| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:36.51 | 3050K| 19226 || 1 | UPDATE | HTZ | 1 | | 0 |00:00:36.51 | 3050K| 19226 || 2 | TABLE ACCESS FULL| HTZ | 1 | 1442K| 1380K|00:00:20.62 | 19674 | 18876 ||* 3 | INDEX RANGE SCAN | IND_HTZ2_OBJECT_ID | 1365K| 886 | 1365K|00:00:07.04 | 1611K| 344 |————————————————————————————————————
这里看到了HTZ1表上面的索引扫描了1365K次range scan，每次是将a.object_id的值传送给htz1的object_id
Predicate Information (identified by operation id):—————————————————3 – access("B"."OBJECT_ID"=:B1)Note—–– dynamic sampling used for this statement (level=2)26 rows selected.
4，更改为merge into语句
www.htz.pw > MERGE INTO /*+ gather_plan_statistics */2 scott.htz a3 USING (SELECT b.object_type, b.owner, b.object_id4 FROM scott.htz1 b) c5 ON (a.object_id = c.object_id)6 WHEN MATCHED7 THEN8 UPDATE SET a.object_type = c.object_type, a.owner = c.owner;1380384 rows merged.Elapsed: 00:00:32.20Execution Plan———————————————————-Plan hash value: 3787432690——————————————————————————————————| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |——————————————————————————————————| 0 | MERGE STATEMENT | | 1272K| 67M| | 21499 (1)| 00:04:18 || 1 | MERGE | HTZ | | | | | || 2 | VIEW | | | | | | ||* 3 | HASH JOIN | | 1272K| 315M| 4584K| 21499 (1)| 00:04:18 || 4 | INDEX FAST FULL SCAN| IND_HTZ2_OBJECT_ID | 88560 | 3545K| | 99 (0)| 00:00:02 || 5 | TABLE ACCESS FULL | HTZ | 1442K| 301M| | 5423 (1)| 00:01:06 |——————————————————————————————————Predicate Information (identified by operation id):—————————————————3 – access("A"."OBJECT_ID"="B"."OBJECT_ID")Note—–– dynamic sampling used for this statement (level=2)Statistics———————————————————-309 recursive calls1419444 db block gets20518 consistent gets6175 physical reads382998348 redo size846 bytes sent via SQL*Net to client1034 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client3 sorts (memory)0 sorts (disk)1380384 rows processedSQL_ID 153haxsb6d8p1, child number 1————————————-MERGE INTO /*+ gather_plan_statistics */ scott.htz aUSING (SELECT b.object_type, b.owner, b.object_id FROMscott.htz1 b) c ON (a.object_id = c.object_id) WHEN MATCHEDTHEN UPDATE SET a.object_type = c.object_type, a.owner = c.ownerPlan hash value: 3787432690——————————————————————————————————————————————–| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |——————————————————————————————————————————————–| 0 | MERGE STATEMENT | | 1 | | 0 |00:00:43.86 | 1439K| 5757 | | | || 1 | MERGE | HTZ | 1 | | 0 |00:00:43.86 | 1439K| 5757 | | | || 2 | VIEW | | 1 | | 1380K|00:00:11.45 | 20025 | 5754 | | | ||* 3 | HASH JOIN | | 1 | 1272K| 1380K|00:00:08.89 | 20025 | 5754 | 7628K| 3091K| 7690K (0)|| 4 | INDEX FAST FULL SCAN| IND_HTZ2_OBJECT_ID | 1 | 88560 | 86275 |00:00:00.15 | 351 | 162 | | | || 5 | TABLE ACCESS FULL | HTZ | 1 | 1442K| 1380K|00:00:01.95 | 19674 | 5592 | | | |——————————————————————————————————————————————–Predicate Information (identified by operation id):—————————————————3 – access("A"."OBJECT_ID"="B"."OBJECT_ID")Note—–– dynamic sampling used for this statement (level=2)
这里能明显的看到HTZ1表上面的索引，只扫描了一次，逻辑读已经下降到20518了。
如果更新的表行数越多的时候，效果就会越明显
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
