Different Database Views
Virtual Views
- A virtual view is a stored SQL query that presents data from one or more tables without storing the data itself.
- Characteristics:
- Dynamic: Data is retrieved in real-time from the underlying tables.
- No Storage: Only the query definition is stored, not the data.
- Use Cases: Ideal for simplifying complex queries and hiding data complexity.
Materialized Views
- A materialized view stores the result of a query as a physical table in the database.
- Characteristics:
- Static: Data is not live; it is refreshed periodically.
- Storage Required: The result is physically stored, which improves query performance.
- Use Cases: Ideal for complex aggregations and data that is queried frequently.
CREATE MATERIALIZED VIEW SalesSummary AS
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID;
Materialized views require maintenance to keep data up-to-date, often through scheduled refreshes.
Hiding Data Complexity
- Simplification: Views abstract complex joins and aggregations, presenting a simplified interface to users.
- Example: A view can combine data from multiple tables, hiding the underlying SQL logic.
