Materialized views vs Normal views

Sign in to answer this question.

• 

  Tuhin Paul1y agoPosted Feb 2, 2025, 4:44 AM

  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
  

  
• 

  Tuhin Paul1y agoPosted Feb 2, 2025, 4:27 AM

  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.
• 

  Shubham Sidnale1y agoPosted Feb 1, 2025, 4:50 PM

  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

   
• 

  Muhammad Imran Ansari1y agoPosted Feb 1, 2025, 3:29 PM

  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