Defining materialized views

In designing your application, consider defining materialized views for frequently-executed expensive queries or expensive parts of your queries, such as those involving intensive aggregation and join operations. Materialized views are designed to improve performance in environments where:

the database is large
frequent queries result in repetitive aggregation and join operations on large amounts of data
changes to underlying data are relatively infrequent
access to up-to-the-moment data is not a critical requirement

You do not have to change your queries to benefit from materialized views. For example, materialized views are ideal for use with data warehousing applications where the underlying data doesn't change very often.

The optimizer maintains a list of materialized views to consider as candidates for partially or fully satisfying a submitted query when optimizing. If the optimizer finds a candidate materialized view that can satisfy all or part of the query, it includes the view in the recommendations it makes for the enumeration phase of optimization, where the best plan is determined based on cost. The process used by the optimizer to match materialized views to queries is called view matching. Before a materialized view can be considered by the optimizer, the view must satisfy certain conditions. This means that unless a materialized view is explicitly referenced by the query, there is no guarantee that it will be used by the optimizer. You can, however, make sure that the conditions are met for the view to be considered.

If the optimizer determines that materialized view usage is allowed, then each candidate materialized view is examined. A materialized view is considered for use by the View Matching algorithm if:

the materialized view is enabled for use by the database server. See Enable and disable materialized views.
the materialized view is enabled for use in optimization. See Enable and disable optimizer use of a materialized view.
the materialized view has been initialized. See Initialize materialized views.
the materialized view meets all the optimizer requirements for consideration. See Materialized views and the View Matching algorithm.
the values of some critical options used to create the materialized views match the options for the connection executing the query. See Restrictions on materialized views.
the last refresh of the materialized view does not exceed the staleness threshold set for the materialized_view_optimization database option. See Setting the optimizer staleness threshold for materialized views.

If the materialized view meets the above criteria, and it is found to satisfy all or part of the query, the View Matching algorithm includes the materialized view in its recommendations for the enumeration phase of optimization, when the best plan is found based on cost. However, this does not mean that the materialized view will ultimately be used in the final execution plan. For example, materialized views that appear suitable for computing the result of a query may still not be used if another access plan, which doesn't use the materialized view, is estimated to be cheaper.

Discuss this page in DocCommentXchange.