How does query rewriting work?
In the world of database optimization, Query Rewrite is essentially a "magic trick" performed by the Oracle Optimizer.
It is a feature where the database automatically intercepts a user's SQL query and redirects it to a Materialized View instead of the original tables. The best part? The user has no idea it happened—they just notice their query finished in 2 seconds instead of 20 minutes.
Imagine you have a SALES table with 500 million rows.
A user runs a query to find the SUM(SALES_AMT) for the year 2025.
Normally, Oracle would have to scan millions of rows to calculate that sum.
However, you have a Materialized View that already contains the pre-calculated sums for every year.
Instead of doing the hard work of scanning the massive SALES table, the Optimizer rewrites the query behind the scenes to pull the answer from the tiny Materialized View.
The Optimizer doesn't just rewrite every query. It follows a strict set of checks to ensure accuracy:
Join Compatibility: Does the Materialized View (MV) contain the same tables and join conditions as the query?
Data Sufficiency: Does the MV have all the columns needed to answer the query?
Grouping Compatibility: If the user is grouping by Month, but the MV only groups by Year, the rewrite won't happen because the data isn't granular enough.
Integrity Level: This is a safety setting (QUERY_REWRITE_INTEGRITY). It tells Oracle: "Are you allowed to use an MV that might be slightly out of date (stale), or must the data be 100% fresh?"
For Query Rewrite to work, three things must be true:
Enabled on the Instance: The database parameter QUERY_REWRITE_ENABLED must be set to TRUE.
Enabled on the MV: When creating the Materialized View, you must include the clause ENABLE QUERY REWRITE.
Cost-Based Optimizer (CBO): The query must be analyzed by the CBO (which is the default in modern Oracle).
Why is this better than just telling the user to query the Materialized View directly?
Simplifies Development: Developers can write simple SQL against "Real" tables without needing to know about the complex MV structure in the background.
Centralized Maintenance: If you decide to drop the MV or change its structure, you don't have to break hundreds of reports. The Optimizer will simply fall back to the original tables.
Massive Performance Gains: It turns $O(n)$ operations (scanning $n$ rows) into $O(1)$ operations (reading one pre-calculated row).
You won't see "Query Rewritten" in your results window. To verify it, you must look at the Execution Plan.
If you see the name of your Materialized View in the "Name" column of the plan—even though you wrote your SQL against the Base Table—then Query Rewrite has successfully taken place.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)|
| 1 | MAT_VIEW ACCESS FULL | MV_SALES_SUM_YEARLY | 1 | 3 (0)|
-----------------------------------------------------------------------------------
The most common reason Query Rewrite fails is Data Integrity. If your source table has changed but the MV hasn't been refreshed yet, Oracle (by default) will refuse to use the "stale" MV because it doesn't want to give the user wrong answers.
You can lower the "strictness" by setting:
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = STALE_TOLERATED;
Use this with caution! Only do this if your users are okay with seeing data that might be a few hours old.
To help the Optimizer rewrite more complex queries, make sure you have Primary Key and Foreign Key constraints enabled (even if they are RELY / NOVALIDATE). This helps the Optimizer understand the relationships between tables so it can safely skip joins.