Consider using materialized views for frequently executed, expensive queries, such as those involving intensive aggregation and join operations. Materialized views provide a queryable structure in which to store aggregated, joined data. Materialized views are designed to improve performance in environments where the database is large, and where frequent queries result in repetitive aggregation and join operations on large amounts of data. For example, materialized views are ideal for use with data warehousing applications.

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. If the optimizer determines that materialized view usage is allowed, then each candidate materialized view is examined. This means that unless a materialized view is explicitly referenced by the query, there is no guarantee that the optimizer uses it. You can, however, make sure that the conditions are met for the view to be considered.

 See also