复盘eygle在甲骨文大会上演讲中的示例，看看什么是大师的由点及面

盖总(eygle)在刚结束的甲骨文大会的演讲中，通过一个简单的UPDATE语句，为我们展示了什么叫由点及面的优化，什么叫由点及面的知识覆盖度，不在于这个案具体如何操作，更应关注或更值得我们借鉴的是这种学习态度和方法思路，大师是如何炼成的？我想这个案例可以带给我们一些启迪。

 

下面就复盘一下这个案例的整个过程，注：版权归盖总(eygle)所有~

 

问题描述：

问题的标题是：“并行更新成为系统瓶颈”

SQL：

点击(此处)折叠或打开

  1. UPDATE /*+ parallel(a, 8) */ tbl_a a
  2. SET name = (SELECT name FROM tbl_b WHERE id = a.id),
  3.         class = (SELECT class FROM tbl_b WHERE id = a.id)
  4. WHERE a.id IN (SELECT /*+ parallel(b, 8) */ id FROM tbl_b b);

现象是这条SQL执行时间非常长，从介绍看是有2.5分钟。

 

优化过程：

1. 为了以下可以更清楚地说明问题，对这个SQL做了简化处理，我们需要优化的是这条SQL：

点击(此处)折叠或打开

  1. UPDATE tbl_a a
  2. SET name = (SELECT name FROM tbl_b WHERE id = a.id),
  3.         class = (SELECT class FROM tbl_b WHERE id = a.id)
  4. WHERE a.id IN (SELECT id FROM tbl_b b);

我们创建两张模拟表：

点击(此处)折叠或打开

  1. SQL> create table tbl_a(
  2.           id number,
  3.           name varchar2(5),
  4.           class varchar2(5));
  5. Table created.
  6. SQL> create table tbl_b(
  7.           id number,
  8.           name varchar2(5),
  9.           class varchar2(5));
  10. Table created.
  11. SQL> create sequence seq_a cache 1000;
  12. Sequence created.
  13. SQL> create sequence seq_b cache 1000;
  14. Sequence created.

插入一些随机数据：

点击(此处)折叠或打开

  1. begin
  2.   for i in 1 .. 100000 loop
  3.     insert into tbl_a values (seq_a.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));
  4.   end loop;
  5.   commit;
  6. end;
  7. /
  8. PL/SQL procedure successfully completed.
  9. SQL> select count(*) from tbl_a;
  10.   COUNT(*)
  11. ------------
  12.      100000
  13. begin
  14.   for i in 1 .. 10000 loop
  15.     insert into tbl_b values (seq_b.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));
  16.   end loop;
  17.   commit;
  18. end;
  19. /
  20. PL/SQL procedure successfully completed.
  21. SQL> select count(*) from tbl_b;
  22.   COUNT(*)
  23. ------------
  24.       10000

2. 执行原SQL语句

点击(此处)折叠或打开

  1. SQL> set timing on
  2. SQL> UPDATE tbl_a a
  3.           SET name = (SELECT name FROM tbl_b WHERE id = a.id),
  4.                  class = (SELECT class FROM tbl_b WHERE id = a.id)
  5.           WHERE a.id IN (SELECT id FROM tbl_b b);
  6. 10000 rows updated.
  7. Elapsed: 00:00:07.42

需要7秒多的时间(虽然和示例中2.5分钟有差距，但仅为了说明优化的问题，时间上的差距可以忽略)。

3. 第一次优化

我们从这个SQL中可以看到，更新TBL_A表的ID列，但TBL_B表的SELECT有三次，即三次的全表扫描，那么要是能减少TBL_B表检索的次数，执行时间肯定可以减少。

点击(此处)折叠或打开

  1. SQL> UPDATE tbl_a a
  2.           SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
  3.           WHERE a.id IN (SELECT id FROM tbl_b b);
  4. 10000 rows updated.
  5. Elapsed: 00:00:04.04

这样的调整是符合SQL语法的，执行时间变为了4秒多，效果显著。

 

4. 第二次优化

虽然执行时间减少了接近一半，但SQL中还是对TBL_B执行了两次扫描，是否还可以减少一次？

点击(此处)折叠或打开

  1. SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  2.                       FROM tbl_a a, tbl_b b
  3.                       WHERE a.id = b.id)
  4.           SET name = b_name, class = b_class;
  5. SET name = b_name, class = b_class
  6.     *
  7. ERROR at line 4:
  8. ORA-01779: cannot modify a column which maps to a non key-preserved table
  9. Elapsed: 00:00:00.01

这样就做到了只扫描一次TBL_B表，直接对子查询更新，但此时报了一个错误，ORA-01779，

这就引出了non key-preserved table的概念。非键值保存表，杨长老的博客(http://blog.itpub.net/4227/viewspace-195889/)中提到过这个错误：

“造成这个错误的原因是更新的列不是事实表的列，而是维度表的列。换句话说，如果两张表关联，其中一张表的关联列是主键，那么另一张表就是事实表，也就是说另一张表中的列就是可更新的；除非另一张表的关联列也是主键，否则这张表就是不可更新的，如果更新语句涉及到了这张表，就会出现ORA-1799错误。如果是两张表主键关联，那么无论更新那个表的字段都可以。

其实这个限制的真正原因是Oracle要确保连接后更新的内容可以写到一张表中，而这就要求连接方式必须是1对N或者1对1的连接。这样才能确保连接后的结果集数量和事实表一致。从而使得Oracle对连接后子查询的更新可以顺利的更新到事实表中。”

a.id=b.id，我们是用TBL_B的id列作为条件更新，需要确保这列只会对应到TBL_B表的一行记录，可以为表TBL_B的id列设置主键、唯一索引或唯一约束，三种操作，这里选择设置唯一约束：

点击(此处)折叠或打开

  1. SQL> alter table tbl_b add constraint uq_b_id unique(id);
  2. Table altered.

再次执行：

点击(此处)折叠或打开

  1. SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  2.                       FROM tbl_a a, tbl_b b
  3.                       WHERE a.id = b.id)
  4.           SET name = b_name, class = b_class;
  5. 10000 rows updated.
  6. Elapsed: 00:00:00.12

执行时间一下仅为0.12秒。

上面如果TBL_A的ID列设置为主键，则为1对1的连接，如果仅是TBL_B的ID列为唯一约束，则为1对N的连接。

 

总结：

通过两次优化，执行时间从7秒降到了0.12秒，虽然这里的示例数据未必和实际情况一致，但成比例的缩放足以说明这个问题，从这个案例可以看出，优化的本质就是少做事，原始SQL执行三次全表扫描，那目标就是减少全表扫描的次数，第一次优化的操作可能相对容易想到，但第二次优化的操作，就需要知道可以有这种语法，而且出现了ORA-01799的错误，还需要知道这种错误的根本原因是什么，才能有可行的解决方法。

 

问题还没完，以上说明了SQL语句的优化，下面就是针对这条SQL展开的知识。

假设上面的TBL_A和TBL_B表是属于用户bisal的，此时新建一个用户phibisal，并授予最简单的权限：

点击(此处)折叠或打开

  1. SQL> create user phibisal identified by phibisal;
  2. User created.
  3. SQL> grant create session to phibisal;
  4. Grant succeeded.

bisal用户创建这两张表的public同义词：

点击(此处)折叠或打开

  1. SQL> create public synonym tbl_a for bisal.tbl_a;
  2. Synonym created.
  3. SQL> create public synonym tbl_b for bisal.tbl_b;
  4. Synonym created.

然后授予phibisal用户对TBL_A表的读和更新权限：

点击(此处)折叠或打开

  1. SQL> grant select, update on tbl_a to phibisal;
  2. Grant succeeded.

此时phibisal登录后执行：

点击(此处)折叠或打开

  1. sqlplus phibisal/phibisal
  2. SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  3.                       FROM tbl_a a, tbl_b b
  4.                       WHERE a.id = b.id)
  5.           SET name = b_name, class = b_class;
  6.                 FROM tbl_a a, tbl_b b
  7.                                     *
  8. ERROR at line 2:
  9. ORA-00942: table or view does not exist

会提示TBL_B不存在，因为用户没有该表的任何权限，(注：此处和eygle的示例中反馈不同，他提示的是ORA-01031: insufficient privileges)
如果授予phibisal对TBL_B表的读权限，

点击(此处)折叠或打开

  1. SQL> grant select on tbl_b to phibisal;
  2. Grant succeeded.

此时可以完成更新：

点击(此处)折叠或打开

  1. sqlplus phibisal/phibisal
  2. SQL> UPDATE tbl_a a
  3.           SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
  4.           WHERE a.id IN (SELECT id FROM tbl_b b);
  5. 10000 rows updated.

但用如下SQL会提示权限错误：

点击(此处)折叠或打开

  1. UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  2.                       FROM tbl_a a, tbl_b b
  3.                       WHERE a.id = b.id)
  4.           SET name = b_name, class = b_class;
  5.                 FROM tbl_a a, tbl_b b
  6.                                     *
  7. ERROR at line 2:
  8. ORA-01031: insufficient privileges

即这种子查询更新会因没有TBL_B表的UPDATE权限报错。
但如果使用如下with语法，则可以正常执行：

点击(此处)折叠或打开

  1. SQL> UPDATE
  2. (WITH tmp AS (
  3.               SELECT b.name b_name, b.class b_class, a.name, a.class
  4.               FROM tbl_a a, tbl_b b
  5.               WHERE a.id = b.id)
  6.              )
  7. SET name = b_name, class = b_class;
  8. 10000 rows updated.

做得更彻底一些：

点击(此处)折叠或打开

  1. SQL> revoke update on tbl_a from phibisal;
  2. Revoke succeeded.

撤消了phibisal用户对TBL_A的更新权限，按理说，phibisal用户不应该能再更新TBL_A表了。
使用上面两个调整后的SQL，确实如此：

点击(此处)折叠或打开

  1. sqlplus phibisal/phibisal
  2. SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class
  3.                       FROM tbl_a a, tbl_b b
  4.                       WHERE a.id = b.id)
  5.           SET name = b_name, class = b_class;
  6.                 FROM tbl_a a, tbl_b b
  7.                                     *
  8. ERROR at line 2:
  9. ORA-01031: insufficient privileges
  10. SQL> UPDATE tbl_a a
  11.           SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)
  12.           WHERE a.id IN (SELECT id FROM tbl_b b);
  13. UPDATE tbl_a a
  14.        *
  15. ERROR at line 1:
  16. ORA-01031: insufficient privileges

但是，奇怪的是如下SQL可以执行：

点击(此处)折叠或打开

  1. SQL> UPDATE
  2. (WITH tmp AS (
  3.               SELECT b.name b_name, b.class b_class, a.name, a.class
  4.               FROM tbl_a a, tbl_b b
  5.               WHERE a.id = b.id)
  6.               SELECT * FROM tmp
  7.              )
  8. SET name = b_name, class = b_class;
  9. 10000 rows updated.

这就从原理规则上，违背了权限控制，看下版本：

点击(此处)折叠或打开

  1. SQL> select banner from v$version where rownum=1;
  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

这就是2014年7月提出的一个bug，在11.2.0.3、11.2.0.4、12.1等版本中都存在的一个问题，需要修正这个bug，相当于使用with语法，可以绕过用户权限，对没有权限的表进行DML操作。

总结：

精髓不在于这个bug，而是在于从一条简单的UPDATE语句，可以派生出如此丰富的知识，可谓举一反三，受益匪浅。一方面需要我们能够从原理上理解每一个概念，另一方面也要培养自己举一反三，知识点由点及面的想法，做到真正的触类旁通，这样才能逐渐向大师靠拢，向大师学习。

时间： 2024-09-20 06:19:38

复盘eygle在甲骨文大会上演讲中的示例，看看什么是大师的由点及面的相关文章

李彦宏在百度联盟大会上演讲

图为百度公司董事长兼首席执行官李彦宏在2012年百度联盟大会上演讲 DoNews 6月1日消息,第七届百度联盟峰会在张家界(000430,股吧)盛召开.百度创始人李彦宏.百度副总裁向海龙.百度副总裁朱光等高层,以及约300名互联网业界精英云集张家界,一起分享百度联盟过去10年的辉煌与成就,畅谈下一个10年的趋势与梦想. 在2012年的联盟峰会上,李彦宏预言了互联网的三大机会,即读图时代.中间页和应用为王.如今看来,这些预言都一一应验.也正因为此,与会嘉宾对李彦宏的趋势预言分外期待.李彦宏表示,移

甲骨文在2011甲骨文全球大会和JavaOne大会上颁发卓越奖

甲骨文将在旧金山举行的2011甲骨文全球大会上表彰利用Oracle解决方案创造商业价值并促进创新的客户与合作伙伴,向表现卓著的客户与合作伙伴颁发Oracle卓越奖(Oracle http://www.aliyun.com/zixun/aggregation/16544.html">Excellence Awards). 甲骨文将在今年的甲骨文全球大会和JavaOne大会上颁发上述奖项,这两个大会于10月2日至6日在美国旧金山举行. 有关Oracle卓越奖的详细信息 Oracle卓越奖包括多

马云将在中国计算机大会上作主题演讲

10月11日上午消息,以"网联世界计算无限"为主题2010中国计算机大会今天在杭州开幕.根据组委会安排,阿里巴巴董事局主席马云将于10月12日上午发表主题演讲. 据透露,马云主题演讲题目为<网络创造美好生活>,他将在演讲中谈论如何发展互联网以及未来新商业文明等话题. 中国计算机大会旨在探讨计算机及相关领域最新进展和宏观发展趋势,展示中国学术界.企业界最重要的学术.技术事件和成果,使不同领域的专业人士能够获得探讨的机会并获得所需信息. 本次大会由中国计算机学会主办,浙江大学和

智谷大会上，来自全球领先专业的专家学者们都分享了什么样的研究成果？

在上个月US News公布的2017最新全球大学排名中,清华大学有4个学科排名全球前10,其中在工程类专业排名第一,计算机科学专业排名第二.那么,在清华的这些院系中又在关注哪些领域,产生了什么样的成果呢?在刚刚召开的首届中国智谷大会上,清华大学智能技术与系统国家重点实验室.清华大学全球产业4.5研究院以及来自计算机系.自动化系等系的老师们精英尽出,为我们分享了在人工智能等领域的最新学术进展.我们可以从中管窥一二: 张钹院士 计算机科学与技术专家,中国科学院院士.主要从事人工智能.人工神经网络.遗

Adobe 在 MAX 大会上发布的这些黑科技，使其股价暴涨近 10%

如同 Google I/O,苹果 WWDC,微软 Build 这样一年一度的开发者大会一样,Adobe 公司也会每年都举行一场属于数字创意人士的盛会,称为 Adobe Max.在这场盛会上,Adobe 往往会发布一些与创意有关的新产品和新技术,同时也会透露公司的未来发展策略. 自然,今年也毫不例外. Adobe Creative Cloud 的几个更新 在当地时间 10 月 18 日的大会主题演讲中,Adobe 公布了旗下创意云 Creative Cloud(简称 Adobe CC) 的几大产品

2016国内十大人工智能演讲盘点，你的男神上榜了吗？

雷锋网 AI 科技评论刚刚出炉了 2016 十大 AI 演讲盘点,不少热心读者感到疑惑:为什么这里面列举的都是国外大牛,怎么不见咱们国内的知名学者们的演讲整理?这不,雷锋网已经整理出了国内过去一年来的精彩演讲,一同随 AI 科技评论看看,哪位大神的高见让你受益匪浅?本文排名不分先后,欢迎各位读者在评论区畅所欲言,说说你们心中的最佳 AI 演讲吧. 高文:回顾过去,展望未来 北京大学信息科学技术学院教授.中国工程院院士.ACM/IEEE Fellow.CCF - GAIR · 全球人工智能与机器人

见证VR产业的元年，一分钟看扎克伯格在Oculus大会上说了啥

10月6日,Oculus的虚拟现实开发者大会Connect 3在美国加州成功召开.这次Oculus为大家带来了一系列最新软硬件产品,其中包括让粉丝们期待了半年的操纵手柄Oculus Touch.在主题演讲中,Facebook总裁扎克伯格的表演最为出彩,他戴上Oculus Rift头盔,拿起手柄Oculus Touch,现场进行了精彩的VR演示. 走入VR时代 2016年可以说是VR产品的爆炸年份,各大科技公司纷纷在VR产品上发力,HTC最早出手,年初就开始预售vive并且取得了相当大的成功. 索

中国网络安全大会上，《网络安全法》起草者是如何解读这部法律的？

本文讲的是中国网络安全大会上,<网络安全法>起草者是如何解读这部法律的?,<网络安全法>生效两周后,北京国家会议中心,正气氛热烈地举行一场安全大会. 中国网络安全大会(NSC2017),由国家相关部委指导,赛可达实验室联合国内外多家具有影响力的行业协会.机构等单位共同主办,今年已经是第五届. 早上九点,北京的天空淅淅沥沥地下着雨,但并不影响观众的热情.嘶吼编辑准点抵达现场时,会场里已经大半坐好了,再过一会,会场快要满席,主持人上台致辞,大会便开始了. NSC2017是<网络安

GMGC2017：一个游戏的大会上 除游戏外我们还能谈什么

一个连手机游戏都不玩的人竟然去参加了一场游戏大会. 上周五,第六届全球游戏大会(GMGC北京2017)在北京国家会议中心闭幕.大会涵盖了游戏.未来科技.直播.电竞.IP联动.VR等多个领域的内容.本来抱着全程听不懂的心情去参会,却意外发现了三个时下谈及最多的IT技术,听演讲嘉宾们探讨它们与游戏的联系. 移动支付和游戏 "目前为止,中国通过手机端每天产生的支付交易的总规模现在已经达到每天1800亿左右.这个数据跟16个月前相比,增长了将近九倍.并且今年一整年的年交易规模也可能会达到一百万亿左右.&