What are materialized views?
In the world of database performance, a standard View is just a saved query—a "shortcut" that runs the underlying code every time you call it. A Materialized View (MV), however, is a view that has been "frozen" into a physical table.
Think of it as the difference between following a recipe to cook a meal (View) and buying a pre-made frozen dinner (Materialized View). The frozen dinner is much faster to serve because the work has already been done.
Materialized Views are used to solve two major problems:
Speeding up massive aggregations: If you have a table with 100 million rows and you frequently need to see "Sales by Region," a standard view would recalculate that sum every time. An MV stores the pre-calculated sums.
Data Replication: They are often used to sync data from a remote database to a local one, allowing users to query a local copy instead of hitting a slow network link.
This is arguably the coolest feature in Oracle. If you have an MV that stores pre-calculated sales totals, and a user runs a query against the original base table, the Optimizer is smart enough to intercept the query.
It says: "Hey, instead of scanning that 1-billion-row table, I'll just pull the answer from this Materialized View." The user doesn't even know they are using an MV, but their query finishes in seconds instead of hours.
Since an MV is a physical copy of the data, it can become "stale" as the original table changes. You have to decide how to refresh it:
FAST Refresh: Uses a "Materialized View Log" to track only the changes (deltas) made since the last refresh. It’s very quick.
COMPLETE Refresh: Truncates the MV and re-runs the entire query from scratch.
ON COMMIT: The MV updates automatically every time the source table is changed (expensive for performance).
ON DEMAND: You manually trigger the refresh or schedule it for 2:00 AM.
| Feature | Standard View | Materialized View |
| Storage | No disk space (just code). | Uses disk space (stores data). |
| Performance | Depends on the underlying query. | Near-instant (data is pre-fetched). |
| Data Freshness | Always 100% current. | Can be "Stale" depending on refresh. |
| Best For | Simplifying complex SQL. | High-speed reporting/aggregation. |
Summaries: When you need to sum, average, or count millions of rows.
Join Heaviness: When you have a query that joins 10+ tables; pre-join them into an MV.
Remote Data: When you need to access data from another server via a Database Link.
CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT region_id, SUM(amount)
FROM sales
GROUP BY region_id;
Peer Tip: Before you can use a
FASTrefresh, you must create a Materialized View Log on the master table. Without that "change log," Oracle won't know which rows changed and will force a slowCOMPLETErefresh