|Oracle9i OLAP Services Concepts and Administration Guide
Release 1 (9.0.1)
Part Number A88755-01
Configuring Your Data Warehouse, 11 of 11
Materialized views store data that is calculated from fact tables. When data in the master tables changes, you can refresh materialized views with the new data.
While a view only stores the query, a materialized view actually stores the results of a query. Thus, there is a trade-off between improved performance and increased storage requirements. Materialized views can greatly improve performance for some types of queries, but can require many additional gigabytes of disk space.
To balance query speed and hardware resources, you should create materialized views for the most frequently calculated aggregates. Less frequently calculated aggregates can continue to be calculated on an as-needed basis. The Summary Advisor Wizard will help you identify the materialized views that your schema should have, within the constraints imposed by the resources you are willing to commit to this use.
The Oracle9i database's query rewrite capability allows it to use materialized views for queries written against fact tables. However, for query rewrite to recognize that the materialized views contain the query results, they must be created using basically the same type of SQL commands that OLAP Services will generate. Oracle Enterprise Manager generates two types of materialized views: those created specifically for use by analytical applications developed using the OLAP API, and those optimized for transactional queries. You must be sure to create materialized views for use by the OLAP API, by following the steps in "Procedure: Running the Summary Advisor wizard" and "Procedure: Creating materialized views".
The Summary Advisor requires that you have already defined dimensions and measures in your metadata. You must also have collected usage data on which Summary Advisor can base its recommendations. Because usage patterns can change, you should rerun Summary Advisor periodically, particularly when the metadata changes or a new application is distributed to users.
Search for the topic "Summary Advisor Prerequisites" in Oracle Enterprise Manager Help for more specific information.
Follow these steps to run the Summary Advisor wizard:
You see the first page of the Summary Advisor wizard.
Note: You can also run the Summary Advisor wizard from the final page of the Create Cube wizard.
The opening page of the Summary Advisor wizard lists the steps you will follow.
Follow these steps to create a materialized view for a cube that you previously created:
You see the Materialized View property sheet.
The following figure shows the General page of the materialized view property sheet.
For more information about the Summary Management wizard, search Oracle Enterprise Manager for "summary management."
For more information about creating materialized views, search Oracle Enterprise Manager for "materialized views."