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.
Step 1: Creating the Base Table
First, we create a base table named T1 with columns KEY (number) and VAL (varchar2(10)). We insert some sample data into this table.
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.
Step 2: Creating an Ordinary View
Next, we create an ordinary view named V, which selects all columns from the table T1.
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.
Step 3: Creating a Materialized View
Now, we create a materialized view named MV. We enable fast refresh and include rowid in the view definition.
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.
Step 4: Comparing Rowid Similarity and Difference
We retrieve the rowids from the base table, ordinary view, and materialized view and order them.
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.
Step 5: Updating the Base Table
We update the values in the base table by converting them to uppercase.
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.
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.
Step 7: Refreshing the Materialized View
We refresh the materialized view using the dbms_mview.refresh procedure.
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.
Step 8: Updating the Base Table via Both Views
We attempt to update the base table through both the ordinary view and the materialized view.
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.
Step 9: Dropping All Objects
Finally, we drop all the objects we created, including the materialized view, ordinary view, and base table.
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.