Re: Materialized Views - Query Rewrite Not Working

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.
3. Build an union MV based on the 2 individual MVs as stated above.
4. Build a table and populate it with aggreagted data from the union MV.
5. Build an MV with prebuilt table clause based on the table from 2.
6. Run your query against the 2-table union view normally.
7. 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 Sat Sep 07 2002 - 13:34:11 CDT