can't merge a union all view

Question

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]

Answer 1

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

Answer 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.

---

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.

Answer 3

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.

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.