Materialized Views for Query Rewrite
Posted on 2003-10-30
Oracle 9i Rel 2 Ver. 188.8.131.52 Windows 2000 Advanced Server
I am administering a datawarehouse and have been asked to improve the performance of reports being created and run by Cognos. I want to create materialized views and use query rewrite. Can somebody explain the steps to plan and implement this?
I have a couple of sample reports. I'm trying to decide whether or not to do this top down or bottom up from the report. I'm thinking it would be better to implement a consistent vision across the whole star schema rather than try to do this one view and one report at a time.
If I don't do top down, the number of tables in the first report is 12. I'm thinking I have to do this in small chunks joining three or four tables in a view. If I do that will all of them have to be full refresh?
Please answer the following questions:
Is it best to look at the data model and implement mviews based on the key relationships between fact and dim? or, look at the (35) reports and try and create mviews that support various reports?
A few tables in the view, or many?
We don't have many space limitations. The db is only about 15 g in size and I have 300 g available. The db is only really used from 8-6 Monday through Friday so I can refresh etc outside those hours.
If I'm not asking the right questions or approaching this correctly please suggest another direction.