Materialized View -Fast Refresh

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

View is a database object that stores a query. It is not a table therefore, it does not store any data but it acts as though it is a table to some users. Most Views are read only and the data cannot be 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 data on a Master table.

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

ON COMMIT: This is an expensive operations which refreshes Materialized View whenever commits happens on Master table. This feature is best when the table has less data with low commit frequency and the data needs replication almost on a real time.

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

There are two types of Materialized View Fast and Complete.

FAST Refresh:  You may already guess what this means which I don’t need to explain it to anyone. Yes, you guessed it correct; it is a fast refresh process. This process utilizes Materialized View Logs that were created on master tables to record all the changes that happened after that last refresh and propagate the changes to underlying table. Since the FAST refresh only moves data that have changed on master table to an the underlying table therefore the refresh is fast. The FAST refresh Materialized View refresh the data that  have changed not all.

A Fast refresh is useful when the data change rate is in low. This is not a great choice when master tables change all the data or the tables are purged frequently. Below, we are going to demo a fast refresh Materialized View.  

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

Master Table : employee:

The Master employee table has 14 records with Primary Key on empno column.

Materialized View Log: A Materialized View log is a database object used for fast refresh. The M-view log keeps track of all the changes that happen to the base table which is employee in our case. This DB object is not needed for COMPLETE refresh because we refresh all the data from masters to underlying tables. The complete refresh is a very expensive operation because it refreshes all the data when you should only refresh the data is changed. Materialized View log capture the change in data on a master table for a fast refresh Materialized View.

Syntax: Materialized View Log:

CREATE MATERIALIZED VIEW LOG ON baniya.employee 
WITH PRIMARY KEY;

Materialized view LOG created.

You don't have to specify the name of a MView log while creation because the system generates by itself. In order to check the detail about the log, you will need to use standard dba_objects view and put MLOG$_master_table_name as object name. This log acts like a table for changed DML. It holds records before MView is refreshed and purged the log 's records after the refresh is completed. 

There are few different types of Materialized View logs and one must be careful to pick the right one for your situations.

PRIMARY KEY: This is by default when the WITH clause is missing. This PRIMARY KEY log stores data changed on PK.

ROW ID: To indicate that the ROWID of all rows changed should be recorded in the Materialized view log.

SEQUENCE: To indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios.
INCLUDING NEW VALUES: Stores old and new data inside the MView log. This is a must setting when working with aggregates data.

Fast Refresh-able Materialized View Syntax:

CREATE MATERIALIZED VIEW employee_mv 
BUILD IMMEDIATE REFRESH FAST ON DEMAND
WITH PRIMARY KEY AS
SELECT empno,
 ename,
 job,
 mgr,
 hiredate,
 sal
FROM employee;
 
Materialized view EMPLOYEE_MV created.

Verify the data of  Materialized View:

SELECT Count(*) 
FROM   scott.employee_mv; 

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

Refreshing Materialized View: A DBMS_VIEW is an Oracle builtin 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 the MView.

DML Change:
BEGIN
 DELETE FROM scott.emp WHERE empno IN(7934, 7902) ;
 COMMIT;
END;
/
PL/SQL procedure successfully completed.

Data Count on Master Table and Mview BEFORE  Mview refresh:

--Master Table Count
SELECT count(*) 
FROM   employee;

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

--Materialized View Count
SELECT count(*) 
FROM   employee_mv;

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

Refreshing Materialized View:

BEGIN
DBMS_MVIEW.REFRESH('SCOTT.EMPLOYEE_MV','F');
END;
/
PL/SQL procedure successfully completed.

Data Count on Master Table and Mview AFTER  Mview refresh:

SELECT Count(*) 
FROM   employee;

COUNT(*) 
---------- 
12 
SELECT count(*) 
FROM   employee_mv;

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

Materialized View details are stored on a  dba_mvews system view. You can check the status, last refresh data time, refresh type, query used and much more.  To check the detail about the log you will need to use dba_objects and put MLOG$_master_table_name as object name. 

Materialized System View:

SELECT * 
FROM   dba_mviews 
WHERE  mview_name LIKE 'EMPLOYEE_MV'; 

Purpose of 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. DBA moves data from Production to Development for testing using Materialized View.

Performance Improvement: Materialized View improves the performance of a query where the query results are stored on a table. The result can be used 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 called causing overhead to the system. The great advantage of a MView 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. First determine the total number of DML changes happening on Master tables, if this number is crazy high, I would refresh at least 3-4 times in 24 hours. If you wait and refresh once daily then the database has to refresh large number data at once which might be an overhead and may issue snapshot too old error. Frequent refresh moves few data which is faster and less overhead to the system. This is one way to fix snapshot too old problem and I encourage this to my readers, developers and database administrators.

 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-5, 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 MView. 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. 

Materialize View Restrictions:

Any simple or complex queries can be converted into a Materialized view. Most complex queries make a complete Materialized View and a simple query makes fast refresh able Materialized View. I would always try to create fast refreshable MView where possible if fast doesn’t work, I would then consider complete refresh. Fast refreshable MView few(yeah right!) limitations and you would need to avoid these to create fast refreshable MView.

 General Restriction on Fast Refresh:

• No SYSDATE or ROWNUM
• No RAW or LONG RAW datatype
• No Subquery on SELECT
• No RANK Function
• No MODEL Clause
• No HAVING Clause
• No ANY, ALL or NOT Exists
• No multiple detail tables at different sites.

 Restriction on Fast Refresh with Aggregates MView:

• All the restrictions from General Restrictions on Fast Refresh.
• Materialized View log must contain all columns from the table referenced in MView. These columns can’t be encrypted. Must specify with ROWID, SEQUENCE and INCLUDING NEW VALUES
• Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX functions are supported.
• COUNT(*) must be specified
• The SELECT can’t have complex queries.
• The SELECT list must contain all GROUP BY columns

 Restrictions on Fast Refresh with Join only MView:

• All the restrictions from General Restrictions on Fast Refresh.
• No GROUP BY clause
• MView log must exist.
• ROWID from all the tables in FROM list must appear in the SELECT query.

I hope you now have a tools to create a fast Materialized View, I encourage you to use Materialized View where possible. If FAST is not an option, create Complete Mview.