Describe what conditions allow for query rewriting.
Query Rewriting with Materialized Views
Oracle materialized views are designed to store precomputed results—joins, aggregations, and summaries—so that the cost-based optimizer (CBO) can answer queries more efficiently. When query rewrite is enabled, the optimizer can substitute a materialized view for the base tables referenced in a user’s SQL, as long as certain conditions are met.
This substitution is transparent: applications still query base tables and views, but the CBO may choose to read from a compatible materialized view instead. The result set is logically equivalent; the main difference is performance.
High-Level Conditions for Query Rewrite
Before Oracle even checks whether a particular materialized view can satisfy a query, several high-level requirements must be met:
Query rewrite must be enabled for the session or system.
Typical settings:
ALTER SESSION SET query_rewrite_enabled = TRUE;
ALTER SESSION SET query_rewrite_integrity = ENFORCED; -- or TRUSTED, STALE_TOLERATED
The query_rewrite_integrity parameter controls how strictly Oracle checks freshness and constraint trust.
The materialized view itself must allow rewrite.
It must be created (or altered) with ENABLE QUERY REWRITE and be in a valid state. If the view is stale beyond what your integrity mode allows, the optimizer will not use it.
The submitted SQL must be logically compatible with the materialized view.
The CBO tests whether the query’s result can be derived from the materialized view’s definition (possibly with additional filtering, aggregation, or joins). Only if that containment test succeeds will the materialized view be considered.
In modern Oracle releases (12c, 19c, 23c/23ai), these rules still apply. Automatic query rewrite can work for both traditional on-premises databases and cloud-based deployments, as long as the optimizer has enough metadata (constraints, statistics, and rewrite settings) to prove correctness.
How the Optimizer Matches Queries to Materialized Views
Once rewrite is enabled, Oracle runs a series of compatibility checks between the query and the materialized view. Conceptually, the materialized view definition looks like:
Materialized View definition
`SELECT col_list
FROM join_list
GROUP BY group_list`
(col_list → col_list)
(join_list → join_list)
(group_list → group_list)
1) If the list of columns, tables to be joined in the query match those in the view (and the GROUP BY clause if aggregates are used), the cost-based optimizer will use the materialized view.
The CBO then attempts general query rewrite. Common successful patterns are illustrated in the remaining images:
2) If additional tables are joined in the query, but the query includes all the joined tables that appear in the materialized view, the cost-based optimizer can still use the materialized view and join the result to the extra tables.
3) If additional columns are mentioned in the query, but they can either be derived from existing summarized columns or retrieved via trusted constraints (for example, primary/foreign key relationships), the optimizer can still use the materialized view.
4) If the materialized view is grouped at a finer level of detail than the query, and the other conditions for use are met, the optimizer can roll the data up further and use the materialized view.
Summarizing the conditions shown in the images:
Exact match: The query’s columns, joins, and GROUP BY list match those of the materialized view.
Extra tables: The query joins additional tables, but all tables from the materialized view are present, allowing the optimizer to join the MV result to the extra tables.
Extra columns: The query requests extra columns that can be derived or fetched via constraints without invalidating the summarized result.
Coarser grouping: The query aggregates to a higher level (coarser granularity) than the materialized view; the optimizer can perform an additional aggregation step on top of the MV.
Freshness, Integrity, and Replication Considerations
Materialized views serve two closely related purposes:
Performance: Precomputing summaries and joins to speed up queries through rewrite.
Replication: Moving or caching data closer to where it is queried (for example, in a reporting or regional database).
In a distributed environment, constantly querying remote tables can create network bottlenecks. Replicating selected data via materialized views reduces cross-database traffic and shortens access paths. However, replicated data becomes stale between refreshes. Query rewrite must therefore balance performance against data freshness.
The query_rewrite_integrity parameter controls this balance:
ENFORCED: Only fresh, fully trusted materialized views are used.
TRUSTED: Allows rewrite when constraints are marked trusted (even if Oracle cannot validate everything automatically).
STALE_TOLERATED: Allows the use of stale materialized views, trading some freshness for performance.
For performance-tuning scenarios, you should choose the strictness level that matches your business requirements for reporting accuracy versus speed.
Example: Creating a Query-Rewrite-Enabled Materialized View
The CREATE MATERIALIZED VIEW statement includes clauses that control refresh and query rewrite behavior. A typical read-only example might look like this:
CREATE MATERIALIZED VIEW local_bookshelf
TABLESPACE users
REFRESH FORCE
START WITH SYSDATE
NEXT SYSDATE + 7
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT *
FROM bookshelf@remote_connect;
Oracle responds with:
Materialized view created.
Key points in this example:
REFRESH FORCE: Oracle prefers a fast refresh, but falls back to complete refresh if necessary.
START WITH / NEXT: Control the refresh schedule (here, every seven days).
WITH PRIMARY KEY: Registers primary key information, which can improve refresh and rewrite options.
ENABLE QUERY REWRITE: Makes the materialized view eligible for automatic substitution by the optimizer.
In practice, most performance-oriented materialized views are read-only. Updatable materialized views introduce additional complexity around conflict detection and multimaster replication and are less common in modern architectures.
Note: The defining query for a rewrite-eligible materialized view should avoid non-deterministic constructs (for example, USER, SYSDATE in the SELECT list, or functions that depend on session state), because these make it harder for the optimizer to guarantee equivalence.
Finally, you can use the EXPLAIN PLAN facility to verify whether a specific query will be rewritten to use a materialized view. The plan output will show the materialized view in place of the base tables when rewrite is successful.
Explain Plan Command
In the next lesson, you will learn how to manage and monitor materialized views over time, including refresh options, storage strategies, and diagnostic tools for rewrite behavior.
