Difference Between View and Materialised View

In this blog post, we will explore the concepts of views and materialized views in a database. We will start by creating a base table and then proceed to create an ordinary view and a materialized view. Throughout the process, we will compare and contrast the behavior and characteristics of these two database objects. Additionally, we will cover topics such as rowid similarity, DML operations, refreshing materialized views, and updating the base table via both views. Let's dive in and enhance the explanation of each step for a better understanding.

First, we create a base table named T1 with columns KEY (number) and VAL (varchar2(10)). We insert some sample data into this table.

create table 

T1(KEY number,VAL varchar2(10)); insert into t1 values(1,'a'); insert into t1 values(2,'b'); insert into t1 values(3,'c'); 

insert into t1 values(4,'');

Explanation:

The base table serves as the foundation for our views and materialized views. It stores the actual data that will be accessed and manipulated through these objects. In this example, the table T1 holds records with a numeric key and a corresponding value. The sample data includes four rows, where the value in the last row is an empty string.

Next, we create an ordinary view named V, which selects all columns from the table T1.

create view v as  select * from   t1 ;

Explanation:

An ordinary view is a virtual table that does not store data on its own. It is defined by a query and provides a way to present the data in the base table in a customized manner. The view V simply retrieves all columns from the table T1. Changes made to the base table will be reflected in the view dynamically.

Now, we create a materialized view named MV. We enable fast refresh and include rowid in the view definition.

create materialized view log on t1 with rowid; create materialized view mv refresh fast with rowid as select * from   t1;

Explanation:

A materialized view is a database object that stores the result of a query in a separate structure. Unlike an ordinary view, a materialized view physically stores the data, making it more suitable for scenarios where real-time data access is not necessary. In this example, we enable fast refresh, which allows for efficient updates when changes are made to the base table. The inclusion of the rowid enables us to compare the rowids of the base table, ordinary view, and materialized view later on.

We retrieve the rowids from the base table, ordinary view, and materialized view and order them.

select rowid from T1 order by rowid ; select rowid from v order by rowid ; select rowid from mv order by rowid ;

Explanation:

Rowids are unique identifiers assigned to each row in a table. By comparing the rowids from the base table, ordinary view, and materialized view, we can observe the similarities and differences between these objects. The ordering of the rowids helps us analyze the data more effectively.

We update the values in the base table by converting them to uppercase.

update t1 set val = upper(val);

Explanation:

By updating the base table, we demonstrate how changes to the underlying data are propagated to the views and materialized views. In this case, we modify the values in the VAL column by converting them to uppercase. The subsequent steps will show how these updates are reflected in the views.

Step 6: Selecting from the Tables and Views after DML

We retrieve the data from the base table, ordinary view, and materialized view, ordering the results by rowid.

select * from T1 order by rowid ; select * from v order by rowid ; select * from mv order by rowid ;

Explanation:

After the update operation in the previous step, we examine the contents of the base table, ordinary view, and materialized view. By ordering the results by rowid, we can compare the changes made to the data and observe the behavior of each object.

We refresh the materialized view using the dbms_mview.refresh procedure.

execute dbms_mview.refresh( 'MV' );

Explanation:

To synchronize the materialized view with the base table and reflect any changes, we refresh the materialized view explicitly. The refresh process updates the materialized view's data based on the query definition. In this example, we use the "MV" parameter to specify the materialized view to refresh.

We attempt to update the base table through both the ordinary view and the materialized view.

update v set val = lower(val); -- View will be create update mv set val = lower(val); -- Here it won't

Explanation:

This step demonstrates the difference in behavior between ordinary views and materialized views when performing updates. We try to update the VAL column in the base table by setting the values to lowercase using both views. While the update via the ordinary view succeeds, the update through the materialized view does not have the same effect.

Finally, we drop all the objects we created, including the materialized view, ordinary view, and base table.

drop materialized view mv ; drop view v ; drop table t1;

Explanation:

To clean up our database environment, we remove the materialized view, ordinary view, and base table. Dropping these objects ensures that they no longer consume resources or interfere with future operations.

By understanding the difference between ordinary views and materialized views, as well as their behaviors and characteristics, we gain insights into how these database objects can be utilized effectively. The comparison between rowids, handling of DML operations, and the process of refreshing materialized views provide a comprehensive understanding of these concepts. Incorporating views and materialized views in database designs allows for flexible data access and efficient data storage and retrieval.