Re: Materialized View vs. Manual summary job

chse30_at_hotmail.com (Christian Svensson) wrote in message news:<ccc2a7eb.0309010026.27465be6_at_posting.google.com>...
> jbradshaw777_at_yahoo.com (John) wrote in message news:<f2f59d82.0308290743.fd15b4d_at_posting.google.com>...
> > Hi Christian,
> >
> > Look into Materilaized view log and become familiar with the concept
> > of "fast refresh", "complete refresh" and "query rewrite".
>
> I have sofar only roughly read about these concepts, but I am not sure
> if the fast refresh mechanism do the new summary as a delta summary
> job using only the new data to refresh it ?
>

Hi Christian,

Let me make sure I understand your situation... You have a fact table(or some fact tabes) and some summary tables in your datamart. You currently have a weekly job that creates the summary tables. The job basically drops the old summary and recreates the new summary. This job is taking a while to complete - even though very little new data has been added to the fact tables. You wonder if the materilaized views can be used to replace the summary tables and boost the performance in refreshing the summary data.

If this is the case, the MV may indeed be able to help - if the summary tables can be replaced with "fast refreshable MV". It will depend on the characteristics of you summary tables. Are they built with data from more than one table? Or are they single-table aggregates? If you post the queries that you use to build the summary tables, people here will be able to further assist you in dtermining whether the MV will be fast refreshable.

"Fast refreshable" simply means that ORACLE will NOT 'trucate' the old data and simply rebuild it. ORACLE will locate the outdated data and replace them with new ones one row at a time. The materialized view logs capture the new data that enable ORACLE to "fast refresh" some MVs.

How does Cognos utilize the summary tables? Can users drill down the see the data in the summary tables? Does Cognos user mannual mention anything about the ORALCE MV?

John Received on Tue Sep 02 2003 - 10:43:58 CDT