What is the difference between a regular virtual data view and a materialized view in a database?

A regular virtual data view is a logical object in a database that represents a stored SQL query. It does not physically store data, but when accessed, the query is executed again, retrieving the current data from the underlying tables.

A materialized view is an object that physically stores the result of a query, meaning the data is saved in a separate table. This allows faster data access, especially if the query is complex and resource-intensive, but requires periodic refresh to synchronize with the underlying tables.

Main differences:

• Data storage: a regular view does not store data, a materialized view does.
• Refresh: a regular view is always current, a materialized view needs to be refreshed manually or on a schedule.
• Performance: a materialized view is faster to read but requires refresh overhead.

Example:

-- Regular view
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- Materialized view
CREATE MATERIALIZED VIEW active_users_mv AS
SELECT * FROM users WHERE status = 'active';
-- To refresh
REFRESH MATERIALIZED VIEW active_users_mv;