Hackforge Academy

Explanation

What is a Materialized View? A Materialized View is a database object that stores the result of a query physically on disk. Unlike a normal view, which executes the query every time it is accessed, a materialized view stores precomputed data.

Code:

-- Create Materialized View
CREATE MATERIALIZED VIEW mv_department_salary AS
SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Explanation

Querying a Materialized View is faster because the data is already stored. It behaves like a table for SELECT operations.

Code:

-- Query Materialized View
SELECT *
FROM mv_department_salary;

Explanation

When the underlying table data changes, the materialized view does not update automatically. It must be refreshed manually.

Code:

-- Refresh Materialized View
REFRESH MATERIALIZED VIEW 
mv_department_salary;

Explanation

A Materialized View is useful for reporting dashboards, monthly summaries, business intelligence reports, and complex aggregations where query performance is more important than real-time data.

Code:

-- Example Reporting Query
SELECT department,
       total_salary
FROM mv_department_salary
ORDER BY total_salary DESC;

Explanation

Difference between View and Materialized View: A View stores only the SQL query and fetches live data every time. A Materialized View stores the query result physically and provides faster reads but requires refresh operations.

Code:

-- Normal View
CREATE VIEW v_department_salary AS
SELECT department,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department;