Differences Between Views and Materialized Views

Feature	View	Materialized View
Definition	A virtual table formed from one or more base tables or views.	A physical copy of the base table or query result.
Storage	Not stored; only displayed when queried.	Stored on the disk as actual data.
Data Update	The data in the view is dynamically updated each time it is queried.	The data in a materialized view must be manually refreshed (or refreshed via triggers).
Processing Speed	Slow processing, as data is queried in real-time from the base tables.	Fast processing, as the data is already precomputed and stored.
Memory Usage	Does not consume memory unless explicitly materialized (e.g., caching).	Utilizes memory because it stores the results physically.
Syntax	CREATE VIEW V AS <query>	CREATE MATERIALIZED VIEW V [BUILD clause] [REFRESH clause] [ON trigger] AS <query>
Use Cases	Typically used in applications to restrict data access or simplify complex queries.	Commonly used in data warehousing or situations requiring periodic, large-scale data retrieval.
Refresh Mechanism	Automatically reflects changes in the underlying tables in real-time.	Must be manually refreshed or done through a scheduled process (can be automatic in some cases).
Performance	Performance can be slower, especially when the underlying data is large and complex.	Performance is generally faster because data is precomputed and stored.
Dependency on Base Tables	Depends directly on the underlying base tables each time it is queried.	Snapshot of the base table(s) at the time of the last refresh.
Data Integrity	Always reflects the most up-to-date data in the base tables.	May be outdated until the next refresh cycle.
Memory Consumption	Does not consume storage or memory unless cached.	Consumes disk space since it stores a physical copy of the data.

Key Differences:

1. Storage and Data:
• View: A view is a virtual table. It doesn’t store any data itself. It dynamically fetches data from the base tables when you query it.
• Materialized View: A materialized view is a physical copy of data. It stores the query results in a separate storage area, and it doesn't query the base tables directly when accessed.
2. Performance:
• View: Queries involving views can be slower because they need to fetch data from the base tables each time they are accessed.
• Materialized View: Since the data is stored, queries are much faster, especially for large datasets, as they don't require re-querying the underlying tables every time.
3. Update Mechanism:
• View: Always shows the most current data because it fetches data in real-time from the underlying base tables.
• Materialized View: Requires manual refreshment (via REFRESH or triggers), meaning that it may show stale data until it is refreshed.
4. Memory Usage:
• View: Does not require memory or storage on disk (except when caching is involved).
• Materialized View: Requires storage space, as it keeps a physical copy of the data.
5. Usage:
• View: Typically used in application-level scenarios to present simplified or restricted views of the underlying database.
• Materialized View: Common in data warehousing or analytics environments, where precomputed data is useful for fast reporting or querying without affecting system performance.
6. Refresh Strategy:
• View: Automatically updates itself whenever you query it (reflects changes in the base tables instantly).
• Materialized View: Needs to be manually refreshed (can be done on-demand or at specific intervals).

Example SQL Syntax:

• View:

CREATE VIEW employee_view AS

SELECT employee_id, first_name, last_name, department

FROM employees

WHERE department = 'Sales';

• Materialized View:

CREATE MATERIALIZED VIEW sales_summary

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

AS

SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;