Cannot combine all kinds of associations

Question

I know that Oracle RDMS cannot combine a view that has a dial statement. I want to know why this is so.

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 converted to 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? [Edit]

Answer 1

The conversion you describe in your edited question seems to me correct.

There are many many different query transformations that the Oracle optimizer can theoretically perform, but in practice this is limited to those transformations that the Oracle team was really trying to implement.

, , , .

, , " ", ; .

Answer 2

, , , . , , tab_p , .

---

SELECT *, .

, .

( UNION'd) tab_p. ( UNION'd) 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, .

Answer 3

. , u .