Materialized views and the View Matching algorithm

The View Matching algorithm determines whether materialized views can be used to satisfy a query. This determination takes place in two steps: a query evaluation step, and a 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

The materialized view definition may contain a GROUP BY clause, and a HAVING clause, provided the HAVING clause does not contain subselects or subqueries.

Note

These restrictions only apply to the materialized views that are considered by the View Matching algorithm. If a materialized view is explicitly referenced in a query, the view is used by the optimizer as if it was a base table.

See also

Query evaluation
Materialized view evaluation
View Matching algorithm examples