Materialized Views in Oracle SQL, covering all the important aspects concisely.
1. Definition:
A Materialized View (MView) is a database object that stores the result of a query physically on disk. Unlike a regular view, which is a virtual table that always reflects the current data of the underlying tables, a materialized view stores a snapshot of the data, which can be refreshed periodically or on demand.
2. Explanation:
Materialized Views are used to improve query performance, especially for complex queries involving joins and aggregations.
They are commonly used in data warehousing scenarios where you need to access data quickly without running the original heavy query each time.
The data in the Materialized View can be updated through refresh mechanisms, which can be set to happen periodically or manually.
3. Structure and Syntax:
Syntax for Creating a Materialized View:
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE]
ON [COMMIT | DEMAND]
START WITH date
NEXT date_expression
AS
SELECT …;
BUILD IMMEDIATE: Populates the Materialized View when it’s created.
BUILD DEFERRED: Populates the Materialized View the first time it is refreshed.
REFRESH FAST: Uses Materialized View Logs to perform incremental refreshes.
REFRESH COMPLETE: Rebuilds the entire Materialized View from scratch.
REFRESH FORCE: Attempts a FAST refresh, but if it fails, a COMPLETE refresh is done.
ON COMMIT: Refreshes the Materialized View when a transaction is committed.
ON DEMAND: Refreshes the Materialized View only when manually invoked.
START WITH & NEXT: Schedules automatic refreshes at specified intervals.
4. Types of Refresh Mechanisms:
1. Complete Refresh:
Recomputes the entire query and updates the Materialized View.
Used when there are no Materialized View Logs or the underlying data has changed significantly.
Slower than FAST refresh.
2. Fast Refresh:
Uses Materialized View Logs to apply only the changes (inserts, updates, deletes) made to the base tables since the last refresh.
Much faster than a complete refresh.
Requires Materialized View Logs to be created on the base tables.
5. Materialized View Log (Essential for Fast Refresh):
A Materialized View Log is a special table that records changes to the base table to support incremental (fast) refreshes. Syntax to Create a Materialized View Log:
WITH ROWID, PRIMARY KEY (columns)
INCLUDING NEW VALUES;
6. Example – Complete Overview:
Step 1: Create Base Table
CREATE TABLE sales (
product_id NUMBER,
quantity_sold NUMBER,
sale_date DATE
);
Step 2: Create Materialized View Log
WITH ROWID, PRIMARY KEY (product_id)
INCLUDING NEW VALUES;
Step 3: Create Materialized View with Fast Refresh
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT product_id, SUM(quantity_sold) AS total_quantity
FROM sales
GROUP BY product_id;
Explanation:
BUILD IMMEDIATE: Populates the view immediately.
REFRESH FAST ON DEMAND: The view can be incrementally updated on demand using Materialized View Logs.
The query aggregates sales data by product_id.
7. Executing a Manual Refresh:
Manual Refresh Example:
EXEC DBMS_MVIEW.REFRESH(‘sales_summary_mv’, ‘FAST’);
FAST: Uses incremental changes captured by Materialized View Logs.
Complete Refresh:
EXEC DBMS_MVIEW.REFRESH(‘sales_summary_mv’, ‘COMPLETE’);
8. Automatic Refresh – Scheduled at Midnight:
To schedule an automatic refresh at midnight every day:
BUILD IMMEDIATE
REFRESH FAST
START WITH TRUNC(SYSDATE) + 1 — Starts at the next midnight
NEXT TRUNC(SYSDATE + 1) — Refreshes every midnight
AS
SELECT product_id, SUM(quantity_sold) AS total_quantity
FROM sales
GROUP BY product_id;
9. Execution and Performance:
Complete Refresh is useful when the data changes drastically or if Materialized View Logs are not available.
Fast Refresh improves performance by applying only the changes, making it suitable for incremental updates.
10. Key Points to Remember:
Materialized View Logs are required for FAST refresh.
Materialized Views help in speeding up complex queries by storing precomputed results.
Scheduled refreshes ensure that data is up-to-date without manual intervention.
They consume extra storage as they physically store the query results, unlike regular views.
Conclusion:
Materialized Views are a powerful feature in Oracle SQL, providing significant performance benefits by storing precomputed query results. Using FAST refresh with Materialized View Logs can optimize refresh times, especially for large datasets. Properly scheduling and managing refresh mechanisms helps maintain up-to-date data while minimizing the performance impact on the database.
