My Oracle Support Banner

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 to [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:

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:


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

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