Tuning Materialized Views with Subqueries and/or Inline Views
(Doc ID 956462.1)
Last updated on FEBRUARY 17, 2019
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 to 188.8.131.52 [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document