Tuning Materialized Views with Subqueries and/or Inline Views (Doc ID 956462.1)

Last updated on FEBRUARY 17, 2019

Applies to:

Goal

This note applies when:

• Creating a materialized view or performing a complete refresh on a materialized view
• The query for the materialized view runs faster stand-alone than in the materialized view
• The query for the materialized view contains subqueries and/or views and/or inline views
• This has been verified on 10.2, 11.1, and 11.2.

When creating materialized views or when doing complete refreshes the query within a materialized view may not perform as well as it does when the query stands alone. This can be due to differences in query transformations if the query uses subqueries and/or views. Query transformations are used by the optimizer to rewrite queries with subqueries and/or views to equivalent queries that can be optimized more effectively than the original query. The stand-alone query takes advantage of cost-based query transformations (CBQT) that are not available for materialized views, resulting in different execution plans. For that reason, it is often necessary to hint the materialized view to achieve the same transformations that occur when the query is run stand alone.

The following hints are useful for forcing query transformations for the materialized view query in order to improve its performance:

• UNNEST, NO_UNNEST, MERGE, NO_MERGE

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.