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 条评论
登录 后参与评论

相关文章

来自专栏MYSQL轻松学

面试中有哪些经典的数据库问题?

1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯...

1120
来自专栏Danny的专栏

《高性能 MySQL》读书笔记

1、隔离级别有四种: READ UNCOMMITTED(未提交读),同事务中某个语句的修改,即使没有提交,对其他事务也是可见的。这个也叫脏读。 READ...

872
来自专栏Java进阶架构师

「mysql优化专题」你们要的多表查询优化来啦!请查收(4)

相信这内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上我就不贴出来了。这图只是让大家回忆一下,各种连接查询。 然后要告诉大家的是,需要根...

462
来自专栏岑玉海

Hive Tunning 补充 关于bucket

  在前面的几篇文章当中一直有一个概念bucketing不清楚到底是怎么回事。   网友南京-李先森给了他收集的一些资料,如下:   Buckets 对指定列计...

2614
来自专栏数据和云

一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)

编辑手记:一条SQL的执行计划异常变更,在深入分析的过程中,发现其涉及到的知识点非常之多,于是整个问题都变得错综复杂。前面介绍了绑定变量及其窥探方面的知识,今天...

3669
来自专栏Java技术

面试中有哪些经典的数据库问题?

1、如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯...

721
来自专栏java达人

Oracle执行计划详解

简介: 本文全面详细介绍oracle执行计划的相关的概念,访问数据的存取方法,表之间的连接等内容。 并有总结和概述,便于理解与记忆! +++ 目录 ...

1707
来自专栏杨建荣的学习笔记

通过IP定位区域的SQL优化思路(r10笔记第10天)

昨天中午吃饭的时候,突然手机收到一条报警信息,提示数据库的负载突然提高了。对于一个高配,稳定,核心的系统来说,出现这么一个报警会立刻引起关注。 ...

3147
来自专栏Albert陈凯

Hadoop数据分析平台实战——130Hive Shell命令介绍 02(熟悉Hive略过)离线数据分析平台实战——130Hive Shell命令介绍 02(熟悉Hive略过)

离线数据分析平台实战——130Hive Shell命令介绍 02(熟悉Hive略过) 导入数据 Hive的导入数据基本上可以分为三类, 第一种是从linux系...

2645
来自专栏java达人

Oracle执行计划详解

简介: 本文全面详细介绍oracle执行计划的相关的概念,访问数据的存取方法,表之间的连接等内容。 并有总结和概述,便于理解与记忆! +++ 目录 ...

24210

扫码关注云+社区