sql - Oracle 12c子查询分解内联 View 现在有不好的计划吗？

Question

更新11/2

经过一些额外的故障排除后，我的团队能够将此Oracle错误直接与在查询停止工作前一天晚上在12c数据库上进行的参数更改相关。在与该数据库绑定的应用程序中遇到一些性能问题后，我的团队让我们的DBA将OPTIMIZER_FEATURES_ENABLE参数从12.1.02更改为11.2.0.4。这解决了有问题的应用程序的性能问题，但导致了我上面描述的错误。作为验证，通过更改此参数，我已经能够在单独的环境中复制相同的问题。我的DBA已向Oracle提交了一份票证，以进行调查。

作为一种解决方法，我可以对查询进行一些更改以检索预期的结果。具体来说，我将Subquery1与Subquery2组合在一起，并且将Subquery1中的一些谓词从WHERE子句移到了JOIN（它们更恰当地属于它们）。这项更改编辑了我的执行计划（效率比以前列出的要低一些），但足以解决原始问题。



原始帖子

首先，对于这个问题的任何含糊之处，让我深表歉意，但是我正在处理一个机密的财务系统，因此我被迫隐藏某些实施细节。

背景

我有一个很早以前就投入生产的Oracle查询，最近从11g升级到12c后，它又同时停止产生预期的结果。据我（和我的生产支持团队）所知，此查询在过去一年中运行良好。

细节

该查询过于复杂且效率不高，但这在很大程度上是因为我正在处理非规范化表（历史上是在大型机之后建模的表）以及来自上游系统的不良数据输入。为了处理复杂的业务情况，我利用了多个级别的子查询分解（WITH语句），然后我的最终语句将两个内联视图连接在一起。没有所有复杂谓词的查询的基本结构如下：

我有3个表Table1，Table2，Table3。 Table1是由Table2的记录组成的处理表。

--This grabs a subset from Table1
WITH Subquery1 as (
   SELECT FROM Table1),

--This eliminates certain records from the first subset based on sister records 
--from the original source table 
Subquery2 as (
   SELECT FROM Subquery1
   WHERE NOT EXISTS FROM (SELECT from Table2)),

--This ties the records from Subquery2 to Table3
Subquery3 as (
   SELECT FROM Table3
   JOIN (SELECT Max(Date) FROM Table3)
   JOIN Subquery2)

--This final query evaluates subquery3 in two different ways and 
--only takes those records which fit the criteria items from both sets
SELECT FROM 
(SELECT FROM Subquery3)             -- Call this Inline View A
JOIN (SELECT FROM Subquery3)        -- Call this Inline View B

最终查询非常简单：

   SELECT A.Group_No, B.Sub_Group, B.Key, B.Lob               
   FROM   (SELECT Group_No, Lob, COUNT(Sub_Group) 
           FROM   Subquery3 
           GROUP BY Group_No, Lob
           HAVING COUNT(Sub_Group) = 1) A 
   JOIN (SELECT Group_No, Sub_Group, Key, Lob
         FROM   Subquery3 
         WHERE  Sub_Group LIKE '0000%') B 
   ON A.Group_No = B.Group_No
   AND A.Lob = B.Lob

问题

如果我编辑最终查询以删除第二个内联视图并评估 A内联视图的输出，则返回0行。我已经手动评估了每个子查询的记录，并可以确认这是预期的结果。

同样，如果我编辑最终查询以仅生成“ B”内联视图的输出，则会得到6个返回的行。同样，我手动评估了数据，这完全符合预期。

现在，当将这两个子集（Inline View A和Inline View B）连接在一起时，我希望最终查询结果将为0行（因为完整集和空集之间的内部连接不会产生匹配项） 。但是，当我如上所述使用内部联接运行整个查询时，我将获得1158行！

我已经审查了执行计划，但没有想到：




问题

显然，我已经做了一些使Oracle优化器感到困惑的事情，并且更新后的查询计划正在拉回与我提交的查询截然不同的查询。我最好的猜测是，由于所有这些临时视图都在同一个查询中浮动，我使Oracle迷惑了要先评估一些依赖于它的集合。

到目前为止，我一直无法在 WITH语句周围找到Oracle官方文档，因此，我对子查询的评估顺序从未完全确定。我确实在搜索SO（现在找不到）时注意到，有人提到一个因式子查询不能引用另一个因式查询。我之前从未听说过这是真的，但是上面的奇怪输出让我怀疑我之前是否对这个查询很幸运？

谁能解释我所看到的行为？我是否要对此查询计划做一些明显不正确的事情？或者，是否有可能在11g和12c之间进行了某些更改，从而可以解释此查询的行为可能已更改的原因？

Answer 1

这听起来像是Oracle中的“错误结果”错误。这些错误通常非常特定于所使用的版本和功能。您发布的查询或执行计划显然没有错。

您有两种方法可以解决此问题：


尝试找到确切的错误。您使用公用表表达式所做的事情看起来不错。在某些情况下，您的查询在技术上是无效的，在少数情况下，您会在一个版本中得到“幸运”字样并且可以正常工作，而在升级时失败。但是当发生这种情况时，新版本通常会引发错误，而不是返回错误的结果。您正在使用的功能可能存在一些非常奇怪的特定组合，从而导致了问题。为了找到真正的问题，您需要大规模简化查询，直到可以进行最小的更改并看到问题出现和消失为止。您还希望删除所有对象，仅使用DUAL。此过程可能需要几个小时。最后，当您只剩下几行代码时，可以将其发布在此处，查看Oracle支持或创建服务请求。
避免该错误。即使您执行了上述步骤，也可能无法解决问题。有时最好的解决方法是做一些不同的事情。深入解决每个问题很高兴，但您并不总是有时间。而是尝试以语法上不同但逻辑上等效的方式重新编写查询。删除一些或所有公用表表达式，甚至可能重复一些SQL。但是请务必发表评论，警告未来的程序员您为什么以奇怪的方式做事。