Oracle笔记：测试update语句关联表扫描的次数

我们的文章会在微信公众号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)