[02] – View vs Materialized View

In this tutorial, you’ll discover the fundamental differences between Views and Materialized Views, including how they handle data storage, retrieval, and performance. By the end, you’ll have a clear understanding of their advantages, limitations, and ideal use cases.

Definition #

Data Storage #

Performance #

Data Freshness #

Use Cases #

• View:
  • Simplifies complex queries by abstracting them into a single, reusable structure.
  • Provides security by restricting access to specific rows or columns through the view definition.
  • Suitable for scenarios where up-to-date data is crucial.
• Materialized View:
  • Used to optimize performance for expensive queries that involve joins, aggregations, or large datasets.
  • Helpful in scenarios where real-time data isn’t required, and periodic updates are acceptable.
  • Ideal for data warehousing and reporting systems.

Refresh Mechanism #

• View: No need to refresh; always reflects the current state of the base tables.
• Materialized View:
  • Can be refreshed manually or automatically at specified intervals.
  • Refresh modes:
    • Complete: Recomputes the entire view.
    • Incremental (a.k.a. Fast Refresh): Updates only the changes since the last refresh.

Example #

• View Example

CREATE VIEW employee_view AS
SELECT id, name, department FROM employees WHERE status = 'active';

Fetches live data from the employees table every time employee_view is queried.

• Materialized View Example

CREATE MATERIALIZED VIEW employee_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT id, name, department, SUM(salary) AS total_salary
FROM employees
WHERE status = 'active'
GROUP BY id, name, department;

Stores the result of the query and can be refreshed on demand or at scheduled intervals.

Summary Table #