Overview of Materialized View Management Tasks

The motivation for using materialized views is to improve performance, but the
overhead associated with materialized view management can become a significant
system management problem. When reviewing or evaluating some of the necessary
materialized view management activities, consider some of the following:

■ Identifying what materialized views to create initially.
■ Indexing the materialized views.
■ Ensuring that all materialized views and materialized view indexes are refreshed
properly each time the database is updated.
■ Checking which materialized views have been used.
■ Determining how effective each materialized view has been on workload
performance.
■ Measuring the space being used by materialized views.
■ Determining which new materialized views should be created.
■ Determining which existing materialized views should be dropped.
■ Archiving old detail and materialized view data that is no longer useful.
After the initial effort of creating and populating the data warehouse or data mart, the
major administration overhead is the update process, which involves:
■ Periodic extraction of incremental changes from the operational systems.
■ Transforming the data.
■ Verifying that the incremental changes are correct, consistent, and complete.
■ Bulk-loading the data into the warehouse.
■ Refreshing indexes and materialized views so that they are consistent with the
detail data.
The update process must generally be performed within a limited period of time
known as the update window. The update window depends on the update frequency
(such as daily or weekly) and the nature of the business. For a daily update frequency,
an update window of two to six hours might be typical.
You need to know your update window for the following activities:
■ Loading the detail data
■ Updating or rebuilding the indexes on the detail data
■ Performing quality assurance tests on the data
■ Refreshing the materialized views
■ Updating the indexes on the materialized views

Posted in: Materialized Views