Re: Materialized Views - Query Rewrite Not Working

Unfortunately, I did not mention that I was on 8i.

You can not have query rewrite with a "union all" MV, it's a 9i thing.

"Jusung Yang" <jusungyang_at_yahoo.com> wrote in message news:42ffa8fa.0209071034.3ab7c1b5_at_posting.google.com...
> Well, maybe something else would work for you.
>
> 1. Build a materialized view that unions the 2 individual MVs on the 2
> tables. Again you are using UNION ALL so you have to do a complete
> refresh. But since 2 two MVs are aggregated views of the based table
> and should be relatively small, a complete refresh of this MV maybe
> OK. You then, instead of query against the union view on the two
> tables, do a simple query against this MV and forget about all the
> QUERY REWRITE stuff.
>
> 2. If you insist on querying against the union view and want to see
> the QUERY REWRITE in action, try this.
> a. Build an union MV based on the 2 individual MVs as stated above.
> b. Build a table and populate it with aggreagted data from the union
> MV.
> c. Build an MV with prebuilt table clause based on the table from 2.
> d. Run your query against the 2-table union view normally.
> e. Schedule a job to refresh data in the prebuilt data by pulling data
> from the union MV.
>
> The aggregated data is all there in the prebuilt table. It just
> depends on whether the CBO is smart enough to use it. If it QUERY
> REWRITE still doe snot kick in, use the /*+ REWRITE */ hint in your
> query. It has to work.
>
>
>
> "AB" <ab_at_zpzpzpzpzp.com> wrote in message
 news:<alc8j3$iht$1$8302bc10_at_news.demon.co.uk>...
> > 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.
Received on Sun Sep 15 2002 - 01:09:21 CDT