Creating Materialized Views

Storing aggregate data

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.

Query rewrite

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".

Summary Advisor prerequisites

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.

Procedure: Running the Summary Advisor wizard

Follow these steps to run the Summary Advisor wizard:

1. If necessary, start Oracle Enterprise Manager and access OLAP management, as described in "Procedure: Accessing OLAP management".

2. Expand the OLAP tree so that you can see the list of defined cubes.

3. Expand the cube whose usage data you want to analyze.

4. Right-click Materialized Views, then choose Summary Advisor from the cascading menu.

   You see the first page of the Summary Advisor wizard.

5. Choose Help if you need more information while running the wizard.

Note: You can also run the Summary Advisor wizard from the final page of the Create Cube wizard.

Figure: Summary Advisor wizard

The following figure shows step 1 of the Summary Advisor wizard.

Procedure: Creating materialized views

Follow these steps to create a materialized view for a cube that you previously created:

1. If necessary, start Oracle Enterprise Manager and access OLAP management, as described in "Procedure: Accessing OLAP management".

2. Expand the OLAP tree so that you can see the list of defined cubes.

3. Expand the cube whose data you want to aggregate in a materialized view.

4. Right-click Materialized Views, then choose Create from the cascading menu.

   You see the Materialized View property sheet.

5. Choose Help if you need more information while completing each page of the property sheet.

Figure: Materialized view property sheet

The following figure shows the General page of the materialized view property sheet.

Related information

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."