Materialized views vs Normal views

Part -2

SQL code that creates both a normal view and a materialized view for active policies

-- Create a normal (virtual) view for active policies with joined customer, vehicle, and claim details.
CREATE VIEW vw_active_policies AS
SELECT 
    c.CustomerID,
    c.Name AS CustomerName,
    c.Email AS CustomerEmail,
    p.PolicyID,
    p.PremiumAmount,
    p.StartDate,
    p.EndDate,
    v.VIN,
    v.Make,
    v.Model,
    v.Year,
    cl.ClaimID,
    cl.ClaimStatus,
    cl.ClaimAmount,
    cl.ClaimDate
FROM 
    CustomerDB.dbo.Customers AS c
JOIN 
    PolicyDB.dbo.Policies AS p ON c.CustomerID = p.CustomerID
JOIN 
    VehicleDB.dbo.Vehicles AS v ON p.VehicleID = v.VehicleID
LEFT JOIN 
    ClaimsDB.dbo.Claims AS cl ON p.PolicyID = cl.PolicyID
WHERE 
    p.EndDate >= GETDATE();  -- Only active policies


-- Create a materialized view for active policies. 
-- (Note: The exact syntax for materialized views may vary by RDBMS. The following is an example.)
CREATE MATERIALIZED VIEW mv_active_policies
AS
SELECT 
    c.CustomerID,
    c.Name AS CustomerName,
    c.Email AS CustomerEmail,
    p.PolicyID,
    p.PremiumAmount,
    p.StartDate,
    p.EndDate,
    v.VIN,
    v.Make,
    v.Model,
    v.Year,
    cl.ClaimID,
    cl.ClaimStatus,
    cl.ClaimAmount,
    cl.ClaimDate
FROM 
    CustomerDB.dbo.Customers AS c
JOIN 
    PolicyDB.dbo.Policies AS p ON c.CustomerID = p.CustomerID
JOIN 
    VehicleDB.dbo.Vehicles AS v ON p.VehicleID = v.VehicleID
LEFT JOIN 
    ClaimsDB.dbo.Claims AS cl ON p.PolicyID = cl.PolicyID
WHERE 
    p.EndDate >= GETDATE()
WITH DATA;  -- Precompute and store the result

Part - 1

Normal (Virtual) Views:

• Definition:
  A normal view is a virtual table that is defined by a SQL query. It does not store data physically; rather, it dynamically generates results when the view is queried.

• Characteristics:

  • On-Demand Computation: Data is computed in real-time each time the view is accessed.
  • Up-to-Date: Always reflects the current state of the underlying base tables.
  • Performance: Can be slower for complex queries since the computation occurs at query time.
  • Storage: Minimal storage requirements as only the SQL query definition is stored.

 

Materialized Views:

• Definition:
  A materialized view is similar to a normal view but stores the precomputed result set physically on disk. It is refreshed periodically or on-demand.

• Characteristics:

  • Precomputed Data: Offers fast query performance since the data is already computed and stored.
  • Staleness: May not reflect the most recent changes in the underlying data until it is refreshed.
  • Performance: Ideal for complex aggregations and joins where real-time computation can be resource intensive.
  • Storage: Requires additional disk space to store the computed data.
  • Refresh Strategies: Can be refreshed automatically (e.g., on a schedule) or manually.

The key difference between materialized views and normal views in SQL lies in how they store and retrieve data:

1. Normal Views

• A view is a virtual table that is based on a SQL query.
• It does not store data physically; instead, it dynamically fetches data from the underlying tables whenever it is queried.
• It always retrieves the latest data from the base tables.
• Performance Impact: Since the data is fetched in real-time, complex views with multiple joins and aggregations can slow down query execution.
• Example:

CREATE VIEW EmployeeView AS

SELECT EmployeeID, Name, Department FROM Employees;

2. Materialized Views

• A materialized view is a physical copy of a query result that is stored in the database.
• Unlike normal views, it does not update automatically when the base table changes. It needs to be refreshed manually or on a schedule.
• It improves query performance because data retrieval is faster (since it’s precomputed and stored).
• Storage Cost: Since data is stored physically, it consumes additional storage space.
• Example (PostgreSQL/Oracle):

CREATE MATERIALIZED VIEW SalesSummary AS

SELECT CategoryID, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY CategoryID;

• Refreshing Data:

REFRESH MATERIALIZED VIEW SalesSummary;

When to Use Which?

Feature	Normal View	Materialized View
Storage	No storage (virtual)	Stored physically
Data Freshness	Always up-to-date	Stale until refreshed
Performance	Slower for complex queries	Faster for read-heavy operations
Use Case	Frequently updated data	Precomputed aggregates, reporting

Normal Views (Standard Views):
    A normal view is a virtual table based on a SELECT query. It does not store data but dynamically retrieves it when queried. No physical storage, slower compared to materialized views and always shows up-to-date data from the base tables.

-- Example
CREATE VIEW ActiveUsers AS
SELECT UserID, UserName FROM Users WHERE IsActive = 1;

Materialized Views:
    A materialized view stores the result of the query physically in the database. Requires disk space since it stores the computed data, faster for read-heavy workloads and needs to be refreshed (manually or automatically) to reflect changes in base tables.

-- Example:
CREATE MATERIALIZED VIEW SalesSummary AS
SELECT Region, SUM(SalesAmount) AS TotalSales
FROM Sales GROUP BY Region;

-- To refresh it, you might run:
REFRESH MATERIALIZED VIEW SalesSummary;

Key Differences Summary:

Feature	Normal View	Materialized View
Data Storage	No (Virtual Table)	Yes (Stored Data)
Query Performance	Slower (Executes query each time)	Faster (Precomputed results)
Always Up-to-date?	Yes	No (Needs refresh)
Use Case	Frequently changing data	Expensive queries, reporting, caching
Refresh Mechanism	Not needed	Manual or automatic refresh