4.7 Summary Advisor

Now that we have discussed all the benefits that materialized views provide, we come to the question: How do you know what materialized views to create? Decision-support applications typically involve a well-defined set of queries. The simplest solution to improve performance of these queries would be to create one materialized view for each query. In practice, the disk space requirements and refresh overhead will soon get prohibitive. With query rewrite you can use the same materialized view to rewrite a large class of queries. However, determining the optimal set of materialized views to create for a bunch of queries can be tricky. The Summary Advisor tool is designed to help with the problem of choosing the materialized views for an application.

The Summary Advisor can be used to determine what materialized views to create for a given set of queries. The Oracle 9 i Summary Advisor is tightly integrated with the query rewrite engine and is aware of the various rules for query rewrite. The Summary Advisor will optimize the materialized views to fit into a storage limit provided by the user.

In order to run the Oracle 9 i Summary Advisor you must have Java enabled in the database, and statistics must have been collected on all the relevant tables and their columns. If you have constraints and dimensions defined in your schema to express your business rules, the Summary Advisor will make better recommendations, because it can take advantage of the more sophisticated...