sqloracle-databasederived-tableinline-view

can't merge a union all view


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]


Solution

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