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形式，在简单的情况下，前两个表形式第一个查询块然后每个表之后引入一个新的查询块，因此优化器策略（大约）进行以下翻译：

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

但我们得到的结果如下：

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