View vs. Materialized View in Oracle SQL

1. View

Definition: A View is a virtual table in Oracle SQL that is based on the result of a SELECT query. It does not store data physically but displays data fetched from underlying base tables whenever accessed.

Explanation: A view simplifies complex queries, encapsulates business logic, and provides a layer of security by controlling data access. Since it doesn't store data, changes made in the underlying tables are immediately reflected in the view.

Example:

-- Creating a simple view to show employee names and departments
CREATE VIEW employee_view AS
SELECT employee_id, employee_name, department_id
FROM employees
WHERE status = 'Active';

-- Querying the view
SELECT * FROM employee_view;

Here, employee_view does not store data but retrieves it from the employees table.

1. Materialized View

Definition: A Materialized View is a database object that stores the result of a query physically on disk. Unlike a regular view, it is periodically refreshed to keep the data up-to-date.

Explanation: Materialized views improve query performance by storing precomputed data, which is especially useful for complex queries, data warehousing, and reporting. Since data is stored, accessing it is faster, but there may be a lag between refreshes.

Example:

-- Creating a materialized view to store sales data
CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT product_id, SUM(quantity_sold) AS total_quantity, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

-- Querying the materialized view
SELECT * FROM sales_summary_mv;

sales_summary_mv stores aggregated sales data. The REFRESH COMPLETE ON DEMAND option indicates that the data is refreshed only when explicitly requested.

Practical Considerations:

Use Views when you need real-time data from the base tables and want to simplify complex queries or enforce data security.

Use Materialized Views when you need faster access to the data, especially for reporting or when querying large datasets that don't change frequently.