Features
CREATE MATERIALIZED VIEW stores the result of a query as a physical table that Oracle can refresh automatically or on demand to speed up complex look-ups.
A materialized view is a precomputed, queryable snapshot of data that lives as a real table on disk. It answers repeated, costly queries with sub-second speed by holding the results in advance.
Use one when dashboards, reports, or APIs repeatedly join large tables like Orders and OrderItems. Pre-aggregation lowers CPU load and query latency without rewriting application SQL.
Combine CREATE MATERIALIZED VIEW with a defining query, choose a refresh mode (ON COMMIT or ON DEMAND), and decide on build timing (IMMEDIATE or DEFERRED).
FAST refresh uses materialized view logs to apply only row changes. COMPLETE refresh rebuilds the entire snapshot. FORCE lets Oracle pick FAST if possible, else COMPLETE.
Call DBMS_MVIEW.REFRESH('mv_name','C') for COMPLETE or 'F' for FAST. Schedule with DBMS_SCHEDULER for hourly or daily jobs.
The sample below pre-aggregates yesterday’s sales so customer dashboards load instantly.
CREATE MATERIALIZED VIEW mv_daily_customer_revenue
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT o.customer_id,
TRUNC(o.order_date) AS order_day,
SUM(o.total_amount) AS daily_revenue
FROM Orders o
GROUP BY o.customer_id, TRUNC(o.order_date);
Index the materialized view’s join and filter columns. Store it in the same tablespace as source data for I/O locality. Monitor LAST_REFRESH_DATE in DBA_MVIEWS.
Forgetting materialized view logs prevents FAST refresh—add logs with CREATE MATERIALIZED VIEW LOG ON Orders. Creating unnecessary COMPLETE refresh jobs wastes resources—switch to ON COMMIT or FAST where possible.
Yes. After creation, add B-tree or bitmap indexes to columns used in WHERE or JOIN clauses to maximize read speed.
No. You must explicitly DROP MATERIALIZED VIEW LOG ON table_name or the logs remain and consume space.
Query SELECT last_refresh_date FROM dba_mviews WHERE mview_name = 'MV_NAME'; to see the timestamp.
.avif){kind=logo}
