How to Use Views and Materialized Views in Oracle

When working with large datasets in Oracle, Views and Materialized Views play a crucial role in improving performance, managing complex queries, and simplifying data access. In this blog, we’ll explore what they are, how they work, and when to use them.

What is a View in Oracle?

A View is a virtual table that does not store data physically but provides a logical representation of one or more tables. It allows users to write simpler queries while abstracting complex joins and filters.

Creating a View

To create a view, you use the CREATE VIEW statement. Here’s a basic example:

CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 10;

This view, employee_view, acts like a table and allows you to fetch data as follows:

SELECT * FROM employee_view;

Benefits of Views

• Security: You can restrict access to sensitive columns by creating views that expose only necessary data.

• Simplicity: Reduces the complexity of queries by abstracting joins and filters.

• Reusability: Developers can use views instead of writing repetitive queries.

Updating Data Through a View

If a view is based on a single table without group functions or joins, you can perform DML (INSERT, UPDATE, DELETE) operations on it:

UPDATE employee_view SET last_name = 'Baddi' WHERE employee_id = 001;

However, certain views (especially those with joins or aggregate functions) are read-only.

What is a Materialized View?

A Materialized View (MV), unlike a regular view, stores data physically. It is used to improve query performance by storing precomputed query results, reducing the need for frequent expensive calculations.

Creating a Materialized View

To create an MV, you use the CREATE MATERIALIZED VIEW statement:

CREATE MATERIALIZED VIEW emp_dept_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Key Features of Materialized Views

• Improved Performance: Since data is precomputed and stored, queries on MVs are much faster than regular views.

• Refresh Mechanisms: MVs must be refreshed to stay up to date. You can choose different refresh strategies:

  • ON COMMIT: Updates the MV whenever the underlying tables are modified.

  • ON DEMAND: Requires manual refresh using:

    EXEC DBMS_MVIEW.REFRESH('emp_dept_mv');
    

  • FAST REFRESH: Updates only changed data using materialized view logs.

When to Use Materialized Views

• When you need faster query performance on complex joins and aggregations.

• When the data is not frequently updated, making periodic refreshes more efficient.

• When dealing with distributed databases, as MVs help replicate data efficiently.

Choosing Between Views and Materialized Views

Feature	View	Materialized View
Storage	No	Yes
Query Performance	Normal	Faster due to precomputed results
Real-time Data	Yes	No (requires refresh)
DML Operations	Yes (in some cases)	No (except in certain updatable MVs)

If your use case demands real-time data with no storage overhead, use Views. If you need faster performance with precomputed data, go for Materialized Views.

Conclusion

Both Views and Materialized Views are essential tools in Oracle databases. Views help simplify complex queries and enhance security, while Materialized Views provide better performance by caching results. The choice depends on your specific requirements – whether you prioritize real-time data access or query performance.

Do you use Views or Materialized Views in your projects? Share your thoughts in the comments!