Skip to content

4. Views and Materialized Views

Views and materialized views provide virtual tables that help with data abstraction, security, and performance optimization.

1. Views

A view is a virtual table based on a SQL query. It does not store data but dynamically fetches results when queried.

Key Features

  • Real-time data: Always reflects the latest data from the underlying tables.
  • Security: Restricts access to specific columns/rows.
  • Simplifies complex queries by storing reusable SQL logic.

Example:

Create a view to show active users only:

CREATE VIEW ActiveUsers AS
SELECT UserID, Name, Email
FROM Users
WHERE Status = 'Active';
Now, querying ActiveUsers is like querying a table:
SELECT * FROM ActiveUsers;

Drawbacks:
- Since it's not stored physically, performance can degrade for complex queries.
- Every time you query a view, the underlying tables are accessed, which may impact speed.

2. Materialized Views

A materialized view is a stored copy of a query result that must be refreshed periodically.

Key Features

  • Improves performance: Data is precomputed and stored.
  • Can be indexed: Unlike normal views, it supports indexing.
  • Refreshable: Can be updated on demand or at scheduled intervals.

Example:

Create a materialized view that stores the total sales per product:

CREATE MATERIALIZED VIEW ProductSales AS
SELECT ProductID, SUM(Amount) AS TotalSales
FROM Orders
GROUP BY ProductID;

To refresh the data manually:

REFRESH MATERIALIZED VIEW ProductSales;

Drawbacks:
- Uses extra storage since data is physically stored.
- Requires periodic updates (manual or automatic refresh).

Comparison Table

Feature View Materialized View
Storage No (virtual table) Yes (physically stored)
Performance Slower (query executed each time) Faster (precomputed data)
Real-time Data Always up-to-date Requires manual/auto refresh
Indexing No Yes
Use Case Security, simplified queries Performance optimization

When to Use What?
- Use a view when real-time data is required and performance is not a concern.
- Use a materialized view when queries are expensive and performance needs to be optimized.