1. Introduction

In Oracle, both Views and Materialized Views (MViews) serve as saved queries that simplify data retrieval and improve abstraction. However, views are virtual and always reflect real-time data, while materialized views store a physical snapshot of the data and can be refreshed periodically.

Understanding the difference is crucial for choosing the right tool for performance, storage, and consistency needs.

2. What is a View?

A View is a virtual table representing the result of a SQL SELECT query. It doesn’t contain any data itself. Instead, when you query a view, Oracle dynamically executes the query behind the view and returns the result set.

Detailed Characteristics:

• Virtual Table (No Storage): A view only stores the SQL query definition. It does not physically store any data on disk.
• Real-Time Data Access: Every time you query a view, Oracle executes the underlying query using the current data from the base tables, ensuring real-time results.
• Used for Abstraction: Views can hide complex joins, business logic, or sensitive columns from end users, promoting modular and maintainable application design.
• Security Mechanism: Views can be used to grant restricted access to a subset of data (e.g., specific columns or rows), allowing more granular permission control.
• Logical Schema Representation: Views can abstract and present a simplified schema to applications without changing the physical database structure.

3. What is a Materialized View?

A Materialized View (MV) is a database object that stores the result of a query physically on disk. Unlike a regular view, which is recomputed every time, a materialized view stores a snapshot of the query result and can be refreshed periodically.

Detailed Characteristics:

• Persistent Data Storage: The data retrieved by the query is saved in a physical structure, similar to a table. This makes querying significantly faster for large or complex datasets.
• Precomputed Results: Ideal for expensive queries involving aggregations, complex joins, and grouping, because the work is already done and stored.
• Manual or Automatic Refresh: The stored data can be synchronized with the base tables using ON DEMAND, ON COMMIT, or SCHEDULED REFRESHES.
• Improves Performance: Great for performance optimization, especially in OLAP systems where analytics or dashboards repeatedly run the same queries.
• Query Rewrite Support: With proper configuration, Oracle can automatically rewrite incoming queries to use materialized views instead of scanning the base tables, leading to significant performance gains.

4. Key Differences: View vs Materialized View

Here’s a detailed comparison of the two:

Feature	View	Materialized View
Storage	No physical storage; only query definition	Stored physically on disk; occupies space
Data Freshness	Always reflects live data	May contain outdated data depending on refresh interval
Performance	Slower for complex queries due to on-the-fly execution	Much faster for complex queries as results are precomputed
Use Case	Abstraction, modular design, security, real-time reports	High-performance reporting, aggregated data, offline snapshots
Indexing	Cannot be indexed	Can be indexed to improve access speed
Refresh Mechanism	Not applicable	Supports manual or automated refreshes
Query Rewrite	Not supported	Supported (with appropriate settings)
Updatability	Can be updatable if simple	Usually read-only; updatable only in specific scenarios
Dependency Management	Invalidated if base tables are dropped	Requires careful management of refresh and logs

5. Use Cases

Detailed Use Cases for Views:

• Abstracting Complexity: Developers can write complex queries involving multiple joins or derived columns once and encapsulate them in a view for reuse.
• Security Layer: Provide access only to specific columns or rows, hiding sensitive data like salaries or internal comments.
• Backward Compatibility: When restructuring a schema, views help maintain backward compatibility for applications expecting the old schema.
• Simplified Access for Reporting: Business users can use views instead of remembering complex queries.

Detailed Use Cases for Materialized Views:

• Data Warehousing: Used extensively in analytical and OLAP environments to store pre-aggregated or pre-joined data.
• Snapshot Reports: Generate end-of-day or monthly summaries (e.g., total sales, total inventory) and store them for fast access.
• Replication and Offline Data Access: Used in distributed environments to maintain read-only copies of data across systems.
• Query Rewrite Optimization: Oracle can transparently replace a complex query with a reference to the materialized view to improve performance.

6. Syntax and Examples

View Example:

CREATE OR REPLACE VIEW employee_details_view AS
SELECT e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

• This creates a reusable view for application developers or reporting tools.
• Always fetches live data from employees and departments.

Materialized View Example:

CREATE MATERIALIZED VIEW region_sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT region_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region_id;

• BUILD IMMEDIATE: Creates and populates the materialized view immediately.
• REFRESH FAST: Performs incremental refresh using a materialized view log.
• ON COMMIT: Refreshes automatically when changes are committed to the base table.

7. Refresh Strategies for Materialized Views

Detailed Types of Refresh Options:

• ON DEMAND: The refresh is triggered manually via DBMS_MVIEW.REFRESH.

EXEC DBMS_MVIEW.REFRESH('region_sales_mv', 'F');

• ON COMMIT: Automatically refreshes the view every time a transaction modifies the underlying table and commits. Best suited for smaller datasets.
• SCHEDULED REFRESH (START WITH / NEXT): Automatically refreshes the MView at defined intervals (e.g., daily, weekly).

CREATE MATERIALIZED VIEW monthly_sales_mv
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 30
AS
SELECT * FROM monthly_sales;

• FAST Refresh: Uses the materialized view log to capture incremental changes and update only affected rows.
• COMPLETE Refresh: Drops and fully recreates the materialized view from scratch.
• FORCE Refresh: Oracle chooses between FAST or COMPLETE based on availability of logs and compatibility.

8. Performance Considerations

• Pros:
  • Zero storage cost.
  • Always reflects the current state of the data.
  • Easy to maintain and flexible.
• Cons:
  • Can degrade performance if based on complex queries or large datasets.
  • No query rewrite or result caching.

Materialized Views:

• Pros:
  • Huge performance improvement for repetitive and complex queries.
  • Data can be indexed, partitioned, and optimized like a regular table.
  • Supports query rewrite to boost speed transparently.
• Cons:
  • Consumes disk space.
  • Needs refresh logic, logs, and scheduling.
  • Might serve stale data if refresh is delayed.

9. Limitations and Design Considerations

Views:

• Complex views may not be updatable (e.g., ones with joins, group by).
• Views don’t support indexes, so performance depends entirely on the base table structure.
• Changes in base table structure (like column renames) can break views.

Materialized Views:

• FAST refresh requires materialized view logs, which may add write overhead to the base tables.
• If refresh fails or is delayed, users may get stale data.
• MViews can’t always be used for transactional applications requiring real-time accuracy.
• Complex MViews might not be refreshable or may only support complete refresh.