Materialized View - Complete Refresh

Have you worked with View before? Yes, I mean just a View not a Materialized View. Let’s refresh the concept of a View before learning about Oracle Materialized View which we may need to compare with View to understand Materialized View.

View is a database object that stores a query not a data. It does not have a table therefore, it does not store any data but it acts as though it is a table to some users accessing to View. Most Views are read only and the data cannot be updated or modified. To update or modify data the base tables of a query must be changed.

A Materialized View is a database object which is a similar to regular View plus much more. A Materialized view has an underlying table which stores query results. Materialized View must be refreshed periodically to get the latest data whenever there is change in a Master table.

Materialized View can be refreshed in two ways ON COMMIT and ON DEMAND.

ON COMMIT: This is a very expensive operations which refreshes MView whenever the commits happens on Master table. This feature is best when the table has less data with low commit frequency.

ON DEMAND: This is a manual Materialized View refresh which is either scheduled by a job or by user whenever there is a need for one. This feature is best for big tables and the number of DML operations is also high.

There are two types Materialized View Fast and Complete.

FAST REFRESH:  You may already guess what this means which I don’t need to explain it to you. Yes, you guessed it correct; it is a fast refresh process. This process utilizes materialized view logs that were created on master tables to read all the changes that happened before that last refresh and propagate the changes underline table. Since the FAST refresh only moves the data have changed on master table to an underline table therefore it is fast refresh.

A fast refresh is useful when the data change occasionally in a low quantity on the master tables. This is not a great choice when master tables change all the data or the tables are purged. Below we are going to demo a fast refresh materialized view. 

We will create Materialized View based on our standard employee table.

COMPLETE Refresh Materialized View: This type of MView refresh everything from Master to MView. There is no log to keep track of DML changes therefore it refresh everything. It might be slow compared to Fast Refresh but it requires very less maintenance.

With this article, we are going to discuss only about COMPLETE refresh Materialized View. We will create a complete MView, refresh it and do the count of data before and after refresh. Are you ready to explore this with me?

Complete Materialized View Syntax:

CREATE materialized mview_name refresh complete AS 
SELECT column1, 
       column1, 
       … 
FROM   table_name;

Master Table: Employee:

Materialized View Syntax:

CREATE materialized VIEW employee_complete_mv 
refresh complete 
AS SELECT empno, 
          ename, 
          job, 
          hiredate, 
          sal, 
          deptno 
   FROM   employee; 

View created..

View Created, How many records are there in a view?

SELECT Count(*) 
FROM   employee_complete_mv; 

  COUNT(*)
----------
        14

Refreshing Materialized View: A DBMS_VIEW is a Oracle inbuilt package which refreshes Materialized View. A refresh moves DML change from master table to MView’s underlying table. There are no DML changes to our master table; therefore there is no point in refreshing MView. Let’s delete few records from master table and then refresh MView.

DML Change:

BEGIN 
    DELETE FROM scott.emp 
    WHERE  empno IN( 7934, 7902 ); 
    COMMIT; 
END; 
/ 

PL/SQL procedure successfully completed.

Table and Materialized view record count BEFORE refresh:

SELECT COUNT(*) 
FROM   employee; --Master Table

  COUNT(*)
----------
        12

SELECT COUNT(*) 
FROM   employee_complete_mv; -- Mview
 
  COUNT(*)
----------
        14

We only have 12 records on employee table and 14 records on the MView.

Refreshing MView:

BEGIN 
    dbms_mview.Refresh('SCOTT.EMPLOYEE_MV', 'C'); 
END; 
/ 

PL/SQL procedure successfully completed.

'C' is for Complete refresh, and F is for Fast.
Table and Materialized view record count BEFORE refresh:

SELECT Count(*) 
FROM   employee; 

  COUNT(*)
----------
        12

SELECT Count(*) 
FROM   employee_mv;

  COUNT(*)
----------
        12

Materialized View details are stored on a system view called  DBA_MVIEWS. You can check the status, last refresh data time, refresh type, query used and much more.

Why do you need Materialized View?

Data Move:  Data collected on OLTP database are required to move to data warehouse or reporting database for various purposes. Materialized view is the best way to move data from one database to other or to different schema.

Performance Improvement: Materialized view improved the performance of a query where the query results are stored on a table. The result can use whenever you need it, the calculation on a query is done once and the result can be used anytime. The regular view calculates the result output every time the view is used causing overhead to the system. The great advantage of View is a fast retrieval of aggregate data because it pre-calculates and store result inside a underlying table.

Index: Materialized view can have index which when used properly improves the search on a MView. The regular view does not let you create index, therefore it uses the indexes from base tables. With MView, you have the flexibility of creating your index which you don’t have with a view. The index on MView does not adversely affect table write operations, if you were to add index on master table that does lot of read and write, it will impact the write operations causing a slowdown and adds overhead to DB engine.

Refresh Frequency: The refresh frequency is very important and should be determined wisely. There is no formula to determine the refresh frequency but I have few rules I set them based on my experience. The compete refresh all the data from master therefore we need to minimize the frequency to one or not more than twice. If there is need to refresh more than twice then you might need to re-factor your database design to make a fast refresh.  

Once the refresh frequency is determined, you need to work with DBA or find out the best time of the day to refresh view. The best time is always to find out the time when your system is less busy. Most company process data during 8:00 - 5:00, therefore find out the off hours for your database and refresh before and after those hours. Your DBA is the best source for finding a time slot for you.

Next, the business rules where your customer determines how fresh data they want in a Materialized View. Some ask for real-time and some can live with a day old data. This is something you may to need to consider while determining the frequency and design.

Restrictions: Are there any? Well complete refreshable Materialized View was born after too many restrictions on fast refreshable MView. There are few or I would say no restrictions at all on what you can and cannot include in a query therefore they are not worth mentioning here.