Oracle 12.2 的连接消除特性

编辑手记:在12.1及以前的版本中,当祖父,父,子表之间有明显的主键和引用完整性约束,只有加入的主键是单个列键时,才能进行连接消除; 但在12.2多列主键也允许发生连接消除,优化器从内联视图中删除父对象,并在子对象和祖父对象之间留下连接。

很多人曾提出这样的问题,在一条SQL语句当中,from 子句所包含的表的顺序对SQL的执行计划或者SQL的性能有没有影响,从粗略的层面来讲,是没有影响的,但有一些特殊的情况可能会产生不一样的结果。

当考虑连接表的顺序时,优化器有几个内置的算法,用于选择表的初始连接顺序,并通过对初始顺序对调整获得最终的连接顺序。假定其他因子相等时,具有最低优先级的标准之一是通过检测from语句中的表的顺序来决定,这样如果在from子句中有足够多的表,就会形成很多个连接表的子集,然后通过改变每个子集中的表的连接顺序,决定最终的表的连接顺序。(参考:https://jonathanlewis.wordpress.com/2006/11/03/table-order/)

上述的参考文章说以前写的,距离现在已经11年了,那时候,还没有人跟我谈到由于SQL子句顺序改变导致的连接表顺序变化的其他情况。而现在,很多转换都在优化器内实现,情况就会变得复杂。所以如果有一些特殊的情况是我没有考虑到的,大家都可以通过留言或其他方式提出来,我再次测试验证。

不久前,我在测试Oracle12.2点新特性的时候,无意中发现了一种改变表的连接顺序的情况。

我当时使用的SQL语句如下:

正如你接下来将看到的三个表,祖父,父,子有明显的主键和引用完整性约束。 这意味着祖父项具有单列主键,父项具有双列主键,子项具有三列主键。 查询沿着它们的主键连接三个表,然后仅从子表中选择数据,因此它是连接消除的一个很好的例子。

在早期版本的Oracle连接中,只有当加入的主键是单个列键时,才能进行消除,因此12.1和更早版本将只能从此三表连接中消除祖父项; 但在12.2多列主键也允许发生连接消除,所以我们可能希望我们从这个查询中获得的计划将消除祖父母表和父表。 执行计划如下:

输出结果跟预期有些偏差, 优化器已经设法消除表的父 - 所以看起来像“单列主键”连接消除已经工作,但“多列”连接消除没有出现。

另一方面,这条语句并没有遵循通常写SQL的规则。 如果遵循通常都书写模式,子句将是顺序child - > parent - > grandparent,按照我期望访问它们的顺序列出表。 改变之后我们再看执行计划:

可见,基于多列主键的连接消除不起作用,可能必须按照列出from子句中的表的顺序菜可以产生预期的结果。

注意:

如果想知道从Oracle语法切换到ANSI语法是否会有所不同,说明如下:使用ANSI语法,如果SQL按照 grandparent - > parent - > child的顺序列出表,祖父项和父项都会被删除 该顺序对于Oracle语法不能正常工作),并且对于child- > parent - > grandparent,只消除父代。 换句话说,两种语法选项都有一个失败点,但它们的失败是反向的。

创建表

创建索引和约束

以下测试不同的连接顺序:

情况一

情况二

情况三

情况四

当然,针对不同的系统统计信息,或I/O标准,扩展区大小,段空间管理或块大小,以及各类参数,结果都可能会发生改变。

如果你想知道为什么传统方式和ANSI语法在进行连接时会选择相反的处理方向,记住,ANSI SQL首先被转换成一个等效的Oracle形式,在简单的情况下,前两个表形式第一个查询块然后每个表之后引入一个新的查询块,因此优化器策略(大约)进行以下翻译:

然后优化器优化内联查询,消除祖父级在父级和子级之间留下联接,最后才允许父级被删除。

但我们得到的结果如下:

在这种形式下,优化器从内联视图中删除父对象,并在子对象和祖父对象之间留下连接 - 因此无需进一步消除。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-02-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

优化SQL查询:如何写出高性能SQL语句

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查...

483
来自专栏java一日一条

优化SQL查询:如何写出高性能SQL语句

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查...

521
来自专栏Kevin-ZhangCG

MySQL数据库实用技巧

培养兴趣   兴趣是最好的老师,不论学习什么知识,兴趣都可以极大地提高学习效率。当然学习MySQL 5.6也不例外。 夯实基础   计算机领域的技术非常强调基础...

131
来自专栏java一日一条

[MySQL] 关于MySQL一些重要的特征

171
来自专栏顶级程序员

优化SQL查询:如何写出高性能SQL语句

1、 首先要搞明白什么叫执行计划? 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语...

4348
来自专栏Java技术

告诉你38个MySQL数据库的小技巧!

培养兴趣 兴趣是最好的老师,不论学习什么知识,兴趣都可以极大地提高学习效率。当然学习MySQL 5.6也不例外。 夯实基础 计算机领域的技术非常强调基础,刚开始...

331
来自专栏PHP技术

MySQL的语句执行顺序

MySQL的语句一共分为11步,如下图所标注的那样,最先执行的总是 FROM操作,最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表,这个虚拟的表作...

35810
来自专栏同步博客

MySQL查询优化

     一个好的web应用,最重要的一点是有着优秀的访问性能。数据库MySQL是web应用的组成部分,也是决定其性能的重要部分。所以提升MySQL的性能至关重...

953
来自专栏鹅厂少年的奇妙之旅

MySQL索引背后的数据结构及算法原理

本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MyS...

2293
来自专栏互联网开发者交流社区

SQL基础用法(实例二)

1187

扫描关注云+社区