When to use materialized views

You should carefully consider the following requirements and settings before using a materialized view:

  • Disk space requirements   Since materialized views contain a duplicate of data from base tables, you may need to allocate additional space on disk for the database to accommodate the materialized views you create. Careful consideration needs to be given to the additional space requirements so that the benefit derived is balanced against the cost of using materialized views.

  • Maintenance costs and data freshness requirements   The data in materialized views needs to be refreshed when data in the underlying tables changes. The frequency at which a materialized view needs to be refreshed needs to be determined by taking into account potentially conflicting factors such as:

    • Rate at which underlying data changes   Frequently or large changes to data renders manual views stale. Consider using an immediate view if data freshness is important.

    • Cost of refreshing   Depending on the complexity of the underlying query for each materialized view, and the amount of data involved, the computation required for refreshing may be very expensive, and frequent refreshing of materialized views may impose an unacceptable workload on the database server. Additionally, materialized views are unavailable for use during the refresh operation.

    • Data freshness requirements of applications   If the database server uses stale materialized view, it presents stale data to applications. Stale data is data that no longer represents the current state of data in the underlying tables. The degree of staleness is governed by the frequency at which the materialized view is refreshed. An application must be designed to determine the degree of staleness it can tolerate to achieve improved performance. For more information about managing data staleness in materialized views, see Setting the optimizer staleness threshold for materialized views.

    • Data consistency requirements   When refreshing materialized views, you must determine the consistency with which the materialized views should be refreshed. See the WITH ISOLATION LEVEL clause of the REFRESH MATERIALIZED VIEW statement.

  • Use in optimization   You should verify that the optimizer considers the materialized views when executing a query. You can see the list of materialized views used for a particular query by looking at the Advanced Details window of the query's graphical plan in Interactive SQL. See Reading execution plans, and Improving performance with materialized views.

    You can also use Application Profiling mode in Sybase Central to determine whether a materialized view was considered during the enumeration phase of a query by looking at the access plans enumerated by the optimizer. Tracing must be turned on, and must be configured to include the OPTIMIZATION_LOGGING tracing type, to see the access plans enumerated by the optimizer. See Application profiling, and Choosing a diagnostic tracing level.

 See also
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0