Re: Materialized Views - Query Rewrite Not Working

Hello,

Yes, this suggestion does work.

Unfortunately, it does not allow fast refresh. These table contain several billion rows so fast refresh is a must.

Thanks for the suggestion.
AB.

"Jusung Yang" <jusungyang_at_yahoo.com> wrote in message news:42ffa8fa.0209051419.5614613_at_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 Sat Sep 07 2002 - 02:07:14 CDT