A virtual view stores a query but not its results, whereas a materialized view physically stores the query results. Virtual views provide current data but may take longer to query; materialized views improve retrieval speed but must be refreshed.
In A3.3 Database programming, a view presents data derived from one or more base tables. It can simplify complex queries, restrict access to selected columns or rows, and provide a useful abstraction from the underlying database structure.
When a virtual view is queried, the database management system executes its stored SELECT statement against the base tables. For example:
CREATE VIEW ActiveStudents AS
SELECT StudentID, Name
FROM Student
WHERE Status = 'Active';
The view does not contain a separate copy of these student records. If a student's status changes in the base table, the next query of ActiveStudents reflects that change.
A materialized view executes its defining query and stores the resulting rows. Later queries can retrieve these precomputed results without repeating expensive joins, aggregations, or calculations. However, changes to the base tables may not appear until the materialized view is refreshed.
| Feature | Virtual view | Materialized view |
|---|---|---|
| Stored content | Query definition | Query definition and results |
| Data currency | Reflects current base-table data | May contain outdated data between refreshes |
| Query performance | Complex query may be executed repeatedly | Usually faster because results are precomputed |
| Storage requirement | Minimal additional storage | Requires storage for result rows |
| Maintenance | No result refresh required | Must be refreshed periodically or after changes |
| Best use | Security, abstraction, and simple reusable queries | Reporting, analytics, and expensive aggregations |
A common misconception is that a materialized view is a complete database backup. It is not: it stores only the result of a particular query and remains dependent on its base data and refresh policy.
Exam technique: For an HL comparison, identify both the storage difference and the trade-off between performance and data currency. A strong answer applies this trade-off to a scenario rather than merely defining both terms.
