Re: Materialized Views - Query Rewrite Not Working

From: Jusung Yang <jusungyang_at_yahoo.com>
Date: 5 Sep 2002 15:19:09 -0700
Message-ID: <42ffa8fa.0209051419.5614613@posting.google.com>

There was no query rewrite, because the optimizer is not yet smart enough to see through it. Of course you can always create another MV that calculates aggregates on the UNION view of the two tables, query rewrite will kick in after that.

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)

"AB" <ab_at_zpzpzpzpzp.com> wrote in message news:<al6poc$qg8$1$830fa795_at_news.demon.co.uk>...
> Hello,
>
> I have two tables and each has a materialized view. If I select an
> aggregated value from either table, it's materialized view is used and query
> rewrite works fine.
>
> These tables are of similar structure and I also have a view sitting on top
> of them, which "union all"s them together.
>
> If I select the same aggregated value from this view, there is no query
> rewrite.
>
> Any ideas?
>
> Cheers,
> AB.
Received on Thu Sep 05 2002 - 17:19:09 CDT