不能合并一个联合所有的视图

I know Oracle RDMS can't merge a view that has a set operator in it. I want to know why is that.

我知道Oracle RDMS不能合并具有set操作符的视图。我想知道为什么。

For example, this:

例如,这个:

SELECT u.*
FROM
 (
  SELECT a.a1    A,
        a.a2    B
   FROM tab_a a
UNION ALL
  SELECT b.b1    A,
         b.b2    B
    FROM tab_b b
)     u,
tab_p p
WHERE p.a = u.a

could be transformed into this:

可以转化为:

SELECT *
FROM
 (
  SELECT a.a1    A,
         a.a2    B
    FROM tab_a a,
         tab_p p
   WHERE p.a = a.a1
UNION ALL
  SELECT b.b1    A,
         b.b2    B
    FROM tab_b b,
         tab_p p
   WHERE p.a = b.b1
)

These two queries are equivalent, right? [edited]

这两个查询是等价的，对吧?(编辑)

3 个解决方案

#1

The transformation you describe in your edited question appears valid to me.

你在你编辑过的问题中所描述的转变对我来说是有效的。

There are many many many different query transformations that the Oracle optimizer could in theory perform, but in practice this is limited to those transformations that the Oracle team have actually bothered to implement.

在理论上，Oracle优化器可以执行许多不同的查询转换，但在实践中，这仅限于Oracle团队实际上费心实现的那些转换。

Each transformation, if added, would require a significant investment in coding and testing, and would only be done if sufficient demand was detected in the paying market.

如果增加每一个转换，都需要在编码和测试上投入大量资金，而且只有在付费市场中检测到足够的需求时才能实现。

So, it's not that it "can't", necessarily; it just doesn't, yet.

所以，这并不是说它“不能”;它只是不。

#2

The queries will produce the same resultset, but the execution plan is likely to be different. I would expect the first query to be more efficient because it is comparing against tab_p once, vs the two times in the second query.

查询将生成相同的resultset，但是执行计划可能不同。我希望第一个查询更有效，因为它与tab_p进行了一次比较，与第二个查询中的两次比较。

---

Previously, both queries used SELECT *, no table alias in either of them.

No, those queries are not equivalent.

不，这些查询是不等效的。

The first will return columns from both the derived table (UNION'd statement) and the tab_p table. The second query will only return values from the derived table (UNION'd statement), and no columns from the tab_p table. It's more obvious if you substitute the table aliases in the place of SELECT *:

第一个将从派生表(UNION d语句)和tab_p表返回列。第二个查询只返回来自派生表(UNION语句)的值，并且不会从tab_p表返回任何列。如果将表别名替换为SELECT *，则更明显:

First query:

SELECT u.*, p.*
  FROM (SELECT a.a1    A,
               a.a2    B
          FROM tab_a a
        UNION ALL
        SELECT b.b1    A,
               b.b2    B
          FROM tab_b b) u,
       tab_p p
 WHERE p.a = u.a

Second query:

SELECT x.*
 FROM (SELECT a.a1    A,
              a.a2    B
         FROM tab_a a,
              tab_p p
        WHERE p.a = a.a
       UNION ALL
       SELECT b.b1    A,
              b.b2    B
         FROM tab_b b,
              tab_p p
        WHERE p.a = b.a) x

There are no tab_p columns in the SELECT clause of the inner query, for the outer query to provide in the ultimate resultset.

内部查询的SELECT子句中没有tab_p列，以便外部查询在最终的resultset中提供。

This:

这样的:

SELECT *
  FROM (SELECT a.a1    A,
               a.a2    B
          FROM tab_a a
        UNION ALL
        SELECT b.b1    A,
               b.b2    B
          FROM tab_b b) u
  JOIN tab_p p ON p.a = u.a

..is equivalent to the first query. It's using ANSI-92 join syntax vs the ANSI-89 syntax used in the first query.

. .等于第一个查询。它使用的是ANSI-92连接语法，而不是第一个查询中使用的ANSI-89语法。

#3

They are not equivalent. The second query will fail, as u is not defined.

它们并非是是等效的。第二个查询将失败，因为u没有定义。