* How does query rewrite work with a union view?


SQL> select * from mvqr1;

        C1 C2         C3
---------- -- ----------
         1 a           1
         2 a           2
         3 a           3
         4 b           1
         5 b           2
         6 c           1
         7 c           7
         8 c           3

8 rows selected.

SQL> select * from mvqr2;

        C1 C2         C3
---------- -- ----------
         1 a          11
         2 a          12
         3 a          13
         4 b          11
         5 b          12
         6 c          11
         7 c          17
         8 c          13

8 rows selected.

SQL> create or replace view mv_view as select * from mvqr1 union all
select * from mvqr2;

View created.

SQL> create materialized view mv_mvqr1
  2  build immediate
  3  enable query rewrite
  4  as select c2, sum(c3) from mvqr1 group by c2;

Materialized view created.

SQL> 
SQL> create materialized view mv_mvqr2
  2  build immediate
  3  enable query rewrite
  4  as select c2, sum(c3) from mvqr2 group by c2;

Materialized view created.

-- Query rewrite
SQL> set autotrace on
SQL> select c2, sum(c3) from mvqr1 group by c2;

C2    SUM(C3)
-- ----------
a           6
b           3
c          11


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=523
          2)

   1    0   TABLE ACCESS (FULL) OF 'MV_MVQR1' (Cost=2 Card=327 Bytes=5
          232)

-- NO query rewrite
SQL> select c2, sum(c3) from mv_view group by c2;

C2    SUM(C3)
-- ----------
a          42
b          26
c          52


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=26)
   1    0   SORT (GROUP BY) (Cost=4 Card=13 Bytes=26)
   2    1     VIEW OF 'MV_VIEW' (Cost=2 Card=335 Bytes=670)
   3    2       UNION-ALL (PARTITION)
   4    3         TABLE ACCESS (FULL) OF 'MVQR1' (Cost=2 Card=8 Bytes=
          24)

   5    3         TABLE ACCESS (FULL) OF 'MVQR2' (Cost=2 Card=327 Byte
          s=5232)


SQL> set autotrace off
SQL> create materialized view mv_mvqr3
  2  build immediate
  3  enable query rewrite
  4  as select c2, sum(c3) from mv_view group by c2;

Materialized view created.

-- Query rewrite
SQL> set autotrace on
SQL> select c2, sum(c3) from mv_view group by c2;

C2    SUM(C3)
-- ----------
a          42
b          26
c          52


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=523
          2)

   1    0   TABLE ACCESS (FULL) OF 'MV_MVQR3' (Cost=2 Card=327 Bytes=5
          232)