What are the differences between Database View and Materialized View?

In the world of database management systems, views are powerful tools that allow users to retrieve and manipulate data in a simplified manner. Oracle, one of the leading database vendors, offers two types of views: database views and materialized views. While both serve similar purposes, there are significant differences between them in terms of their functionality, storage, and performance. In this article, we will explore these differences with a focus on Oracle's database views and materialized views, along with an example to illustrate their usage

A) Definition and Functionality:

1. Database View: A database view is a virtual table that is derived from one or more underlying tables or views. It provides a logical representation of the data and can be used to simplify complex queries by predefining joins, filters, and aggregations. Whenever a query is executed against a view, the underlying data is dynamically fetched from the base tables.

2. Materialized View: A materialized view, on the other hand, is a physical copy or snapshot of a database view. Unlike a database view, it stores the result set of a query in a separate table, making it a persistent object. Materialized views are typically used to improve query performance by precomputing and storing the results of expensive and complex queries, reducing the need for re-computation during subsequent queries.

B) Data Storage:

1. Database View: Database views do not store any data on their own. They are stored as a definition or metadata in the database dictionary and do not occupy any additional storage space. Whenever a query is executed against a view, it is processed in real-time by retrieving the data from the underlying tables.

2. Materialized View: Materialized views store their data in physical tables, which are separate from the base tables. This storage mechanism allows for faster data retrieval, as the results of the query are already computed and stored. Materialized views occupy disk space to store their data, and this storage requirement should be considered when designing the database schema.

C) Query Performance:

1. Database View: Database views are advantageous for simplifying complex queries and enhancing data abstraction. However, they may suffer from performance issues when dealing with large datasets or queries involving multiple joins and aggregations. Since the data is fetched from the underlying tables dynamically, the execution time may be slower compared to materialized views.

2. Materialized View: Materialized views excel in terms of query performance. By storing the precomputed results of a query, they eliminate the need for repetitive calculations. As a result, subsequent queries against materialized views can be significantly faster compared to database views. Materialized views are particularly useful when dealing with queries that involve extensive processing or access to remote data sources.

Now, let's consider an example to better understand the differences:

Suppose we have a database with two tables: "Orders" and "Customers." We want to create a view that displays the total order amount for each customer. Here's how we can achieve this using both a database view and a materialized view in Oracle:

Database View:

CREATE VIEW Total_Order_Amount AS

SELECT c.Customer_Name, SUM(o.Order_Amount) AS Total_Amount

FROM Customers c

JOIN Orders o ON c.Customer_ID = o.Customer_ID

GROUP BY c.Customer_Name;

Materialized View:

CREATE MATERIALIZED VIEW MV_Total_Order_Amount

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

AS

SELECT c.Customer_Name, SUM(o.Order_Amount) AS Total_Amount

FROM Customers c

JOIN Orders o ON c.Customer_ID = o.Customer_ID

GROUP BY c.Customer_Name;

In this example, the database view "Total_Order_Amount" does not store any data and retrieves it in real-time when queried. On the other hand, the materialized view "MV_Total_Order_Amount" stores the computed results of the query, enabling faster retrieval in subsequent queries. However, the materialized view needs to be refreshed to synchronize the data with the underlying tables. The "REFRESH FAST ON COMMIT" option ensures that the materialized view is updated automatically when changes are committed to the base tables.

While both database views and materialized views offer a convenient way to retrieve and manipulate data, they differ in their functionality, storage mechanisms, and query performance characteristics. Database views provide a logical representation of the data and are suitable for simplifying complex queries, while materialized views offer improved performance by persistently storing precomputed results. Understanding these differences is crucial for making informed decisions when designing and optimizing database systems in Oracle.

 

Learn more about Oracle here