🔧 Materialized View in SQL

Materialized View

Definition:

A Materialized View is a physical copy (snapshot) of the result of a SELECT query. Unlike regular views, materialized views store the actual data and can be periodically refreshed to keep the data up-to-date.

Explanation:

Materialized views are useful for improving performance, especially for complex queries, aggregations, or when data is spread across multiple tables.

Since data is stored on disk, querying a materialized view is faster than querying a complex view because there’s no need to recompute the result each time.

Materialized views need to be refreshed to reflect changes in the underlying tables. This can be done manually, on demand, or at scheduled intervals.

Example:

Suppose we have a sales table:

CREATE TABLE sales (
sale_id NUMBER,
product_id NUMBER,
quantity_sold NUMBER,
amount NUMBER,
sale_date DATE
);

INSERT INTO sales VALUES (1, 101, 5, 500, '2024-10-01');
INSERT INTO sales VALUES (2, 102, 10, 1500, '2024-10-02');
INSERT INTO sales VALUES (3, 101, 7, 700, '2024-10-03');

Creating a Materialized View:

-- Create a materialized view to store the total quantity sold and total sales per product
CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT product_id, SUM(quantity_sold) AS total_quantity, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Using the Materialized View:

-- Querying the materialized view
SELECT * FROM sales_summary_mv;

Output:

Explanation:

The sales_summary_mv materialized view aggregates data from the sales table, storing the total quantities and sales for each product.

The BUILD IMMEDIATE clause means the data is immediately calculated and stored.

The REFRESH COMPLETE ON DEMAND clause means the data in the materialized view will not automatically update when the sales table changes; you need to manually refresh it.

Manual Refresh:

--Refreshing the materialized view manually
EXEC DBMS_MVIEW.REFRESH('sales_summary_mv');

Views vs Materialized views

Use Views when you need real-time data from the base tables and want to simplify complex queries or enforce data security.

Use Materialized Views when you need faster access to the data, especially for reporting or when querying large datasets that don't change frequently.

Materialized Views are a crucial feature in Oracle SQL that helps improve performance by storing precomputed query results. I'll explain Materialized Views in detail, including how to create, manually refresh, and set them up to refresh at regular intervals, such as every midnight.

Materialized Views (MViews)

1. What is a Materialized View?

A Materialized View is a database object that stores the result set of a query physically on disk. Unlike regular views, which are virtual and dynamically fetch data from the base tables whenever queried, a materialized view captures the data at a specific point in time and saves it. This allows for much faster access to data, especially for complex queries.

Key Benefits:

Performance Boost: Complex queries that join multiple tables or involve heavy computations can be precomputed and stored, reducing the load on the database when the query is executed frequently.

Data Warehousing: Materialized views are commonly used in data warehousing environments for storing summarized or aggregated data, making it easier and faster to generate reports.

Data Synchronization: Useful for replicating data across databases or creating read-only copies of data.

1. Syntax to Create Materialized Views

CREATE MATERIALIZED VIEW materialized_view_name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE]
[ON COMMIT | ON DEMAND | START WITH NEXT ]
AS
SELECT ... -- Query that defines the materialized view

BUILD IMMEDIATE: Creates and populates the materialized view immediately.

BUILD DEFERRED: Creates the materialized view definition but does not populate it until it is explicitly refreshed.

REFRESH:

FAST: Only applies the incremental changes (requires materialized view logs).

COMPLETE: Recreates the entire result set from scratch.

FORCE: Attempts fast refresh; if that’s not possible, it does a complete refresh.

ON COMMIT: Refreshes the materialized view whenever a transaction on the base table is committed.

ON DEMAND: Requires explicit, manual refresh.

START WITH/NEXT: Specifies a schedule for periodic automatic refresh.

1. Example: Creating a Materialized View

Suppose we have the following sales table:

CREATE TABLE sales (
sale_id NUMBER,
product_id NUMBER,
quantity_sold NUMBER,
amount NUMBER,
sale_date DATE
);

-- Sample data
INSERT INTO sales VALUES (1, 101, 5, 500, '2024-10-01');
INSERT INTO sales VALUES (2, 102, 10, 1500, '2024-10-02');
INSERT INTO sales VALUES (3, 101, 7, 700, '2024-10-03');

Creating a Materialized View:

-- Materialized view to store the total sales and quantities sold per product
CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT product_id, SUM(quantity_sold) AS total_quantity, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Explanation:

BUILD IMMEDIATE: The materialized view is created and populated right away.

REFRESH COMPLETE ON DEMAND: The data in the materialized view is not automatically updated. You must manually refresh it when needed.

1. How to Refresh Materialized Views

Manual Refresh: You can manually refresh the materialized view using the following command:

-- Manually refresh the materialized view
EXEC DBMS_MVIEW.REFRESH('sales_summary_mv');

Automatic Refresh at Midnight (Every Day): You can set up the materialized view to refresh automatically every midnight using the START WITH and NEXT clauses:

-- Create a materialized view that automatically refreshes every day at midnight
CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE
NEXT TRUNC(SYSDATE + 1) + 1/24
AS
SELECT product_id, SUM(quantity_sold) AS total_quantity, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Explanation:

START WITH SYSDATE: Specifies when the first refresh should occur. SYSDATE means it starts immediately.

NEXT TRUNC(SYSDATE + 1) + 1/24: This schedules the refresh to happen every day at midnight. TRUNC(SYSDATE + 1) moves the date to the start of the next day, and adding 1/24 sets it to midnight (12:00 AM).

1. Extra Key Points

2. Types of Refresh:

Complete Refresh: Deletes all data in the materialized view and recreates it from the base tables. Use this for small data sets or when incremental changes are not feasible.

Fast Refresh: Applies only the changes made since the last refresh (requires materialized view logs to track changes). It’s more efficient for large data sets.

Force Refresh: Tries fast refresh first. If that fails, it falls back to a complete refresh.

1. Materialized View Logs:

To enable fast refresh, you need to create a materialized view log on the base tables. This log records changes to the data (inserts, updates, deletes).

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (product_id, quantity_sold, amount);

1. Query Rewrite:

Materialized views can be used by the Oracle optimizer to rewrite queries and improve performance without modifying the actual query. This is useful for queries that can benefit from pre-aggregated or precomputed data in the materialized view.

Summary Table

Conclusion

Materialized views are an essential tool for improving database performance, especially when dealing with complex, frequently run queries. By understanding how to create, manage, and refresh materialized views, you can optimize database operations and enhance the performance of your applications.