Materialized views and view matching

The optimizer uses a View Matching algorithm to determine whether materialized views can be used to satisfy a query. The determination involves a query evaluation step, and a materialized view evaluation step.

Query evaluation step

During query evaluation, the View Matching algorithm examines the query. If any of the following conditions are true, materialized views are not used to process the query.

All the tables referenced by the query are updatable.

The optimizer does not consider materialized views for a SELECT statement that is inherently updatable, or is explicitly declared in an updatable cursor. This situation can occur when using Interactive SQL, which utilizes updatable cursors by default for SELECT statements.
The statement is a simple DML statement that uses optimizer bypass and is optimized heuristically. However, you can force cost-based optimization of any SELECT statement using the FORCE OPTIMIZATION option of the OPTION clause.
For queries contained inside stored procedures and user-defined functions, the query's execution plan has been cached. The database server may cache the execution plans for these queries so that they can be reused. For this class of queries, the query execution plan is cached after execution. The next time the query is executed, the plan is retrieved and all the phases up to the execution phase are skipped.

Materialized view evaluation step

The optimizer includes a materialized view in the set of materialized views to be examined by the View Matching algorithm if the view definition:

contains only one query block
contains only one FROM clause
does not contain any of the following constructs or specifications:
- GROUPING SETS
- CUBE
- ROLLUP
- subquery
- derived table
- UNION
- EXCEPT
- INTERSECT
- materialized views
- DISTINCT
- TOP
- FIRST
- self-join
- recursive join
- FULL OUTER JOIN
(optionally) contains a GROUP BY clause, and a HAVING clause, provided the HAVING clause does not contain subselects or subqueries.

In addition to meeting the view definition criteria:

the materialized view must be enabled for use by the database server
the materialized view must be enabled for use in optimization
the materialized view must be initialized (populated with data)
values for some critical options used to create the materialized views must match the options for the connection executing the query
last refresh of the materialized view can not have exceeded the staleness threshold set for the materialized_view_optimization database option

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 less expensive.

Materialized views and view matching

Query evaluation step

Materialized view evaluation step

See also