无法合并联合所有视图

发布于 2024-09-24 23:48:38 字数 550 浏览 8 评论 0原文

我知道 Oracle RDMS 无法合并其中包含集合运算符的视图。我想知道这是为什么。

例如，这个：

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

可以转换成这样：

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
)

这两个查询是等价的，对吧？ [编辑]

原文

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

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]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风尘浪孓 2024-10-01 23:48:38

查询将产生相同的结果集，但执行计划可能不同。我希望第一个查询会更高效，因为它与 tab_p 进行比较一次，而与第二个查询中的两次比较。

以前，两个查询都使用 SELECT *，其中都没有表别名。

不，这些查询并不等同。

第一个将从派生表（UNION 语句）和 tab_p 表中返回列。第二个查询将仅返回派生表（UNION 语句）中的值，而不返回 tab_p 表中的列。如果用表别名替换 SELECT * 的位置，效果会更明显：

第一个查询：

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

第二个查询：

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

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

这：

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

..相当于第一个查询。它使用 ANSI-92 连接语法与第一个查询中使用的 ANSI-89 语法。

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.

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 *:

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.

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.

回复收藏 0 原文