Fast Refreshable Materialized View Problem

There are things that schools or training center do not teach you about a Materialized View. They only teach you to create and refresh a fast or complete Materialized View. Whatever, we are going to explore the tips and trick and issue on MView from someone who has been in IT for long working heavily with Materialized view. Oracle documents talks on some restrictions about Materialized view but again it does not talk about the problem that you may experience while working with Materialized View.

We are going to explore two most common problem and their solutions. Knowing these problems before designing database helps you better architect your database which saves time, cost and money. As the topic says, "Fast Refresh Problem", this only applies to Fast Refresh-able Materialized View. We will explore by asking two  simple questions:

1. What happens when you truncate a master table? Will it refresh?
2. What happens when you drop a partition from a master table? Will it refresh?

Truncate Master Table: To re-create this scenario, we have a fast refreshable Materialized View using employee as Master table.Below, we will write a script to:

1. Create a MView Log and  Fast MView,
2. Refresh it FAST,
3. Truncate master table,
4. And refresh it again

Mview Log:

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

Materialized View:

CREATE MATERIALIZED VIEW scott.employee_fast_mv 
BUILD IMMEDIATE REFRESH FAST ON DEMAND
WITH PRIMARY KEY AS
SELECT employee_id,
 first_name,
 last_name,
 email,
 manager_id,
 department_id
FROM scott.employee;

Fast Refresh Mview:

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

Mview Record Count:

Select count(*) from scott.employee_fast_mv;
COUNT(*)
----------
107

Truncate Master Table:

TRUNCATE TABLE scott.employee;
Table SCOTT.EMPLOYEE truncated.

Fast Refresh Again:

BEGIN
DBMS_MVIEW.REFRESH('SCOTT.EMPLOYEE_FAST_MV','F');
END;
/

Output:

Error starting at line : 5 in command -
BEGIN
 DBMS_MVIEW.REFRESH('EMPLOYEE_FAST_MV','F');
END;
Error report -
ORA-32321: REFRESH FAST of "PERMIT_P"."EMPLOYEE_FAST_MV" unsupported after detail table TRUNCATE
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 2
32321. 00000 -  "REFRESH FAST of \"%s\".\"%s\" unsupported after detail table TRUNCATE"
*Cause:    A detail table has been truncated and no materialized view
           supports fast refersh after a detail table has been truncated
*Action:   Use REFRESH COMPLETE.  Note
           materialized view does not support fast refresh after TRUNCATE using
           the DBMS_MVIEW.EXPLAIN_MVIEW() API.
Elapsed: 00:00:00.865

There are two ways to overcome this issue. The first one is to refresh complete and the second one is to convert FAST refresh view to a FORCE refresh view. We will apply both the solutions to resolve the issue and talk about what which one to pick for your situation.

Refreshing View Using Complete 'C':

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

Data Cleanup:

DROP MATERIALIZED VIEW LOG ON scott.employee;
DROP MATERIALIZED VIEW scott.employee_fast_mv;

Complete refresh works fine and after the complete refresh, you can switch back to fast refresh. Now, in some case, your refresh is automated from scheduled jobs. You may need to write additional logic to check the error and decide for a complete or fast refresh. If the refresh is not scheduled, you will need to remember to refresh complete whenever master table data gets truncated. With a busy company, this isn't  possible therefore, we have another solution is to convert into FORCE Refresh view. This view tries to refresh Fast if fails, it will refresh Complete. DBA/Developer do not need to add additional logic to their code. I prefer this solutions over complete refresh and you will know why soon.

Solution 2: Force Refresh View:
We will convert our existing MView (scott.employee_fast_mv) to FORCE REFRESH. Again, we will repeat all the steps we performed for Fast refreshable View to see if we see any problem.

Mview Log:

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

Force Refresh Mview DDL:

CREATE MATERIALIZED VIEW scott.employee_force_mv 
BUILD IMMEDIATE REFRESH FAST ON DEMAND
WITH PRIMARY KEY AS
SELECT employee_id,
 first_name,
 last_name,
 email,
 manager_id,
 department_id
FROM scott.employee;

Refresh Force View: '?'

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

MView Record Count:

Select count(*) from scott.employee_force_mv;
COUNT(*)
----------
107

Truncate Master Table:

TRUNCATE TABLE scott.employee;
Table SCOTT.EMPLOYEE truncated.

Refresh Force MView:

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

MView Count:

Select count(*) from scott.employee_force_mv;

COUNT(*)
----------
0

This is the best solution which is most recommended. The next question we asked at the beginning what happened when we drop a partition from a master table. We will get the same error message and number like we got for Truncate. The same two solutions we discussed about applies to this issue as well. 

You know the best solution to your problem. Next, let's get some detail about FORCE REFRESH. What is a force refresh? Why do you need it? To understand force refresh you must have a complete understanding of FAST and Compete refresh. If you need to refresh these concepts, please visit complete & fast refresh using the site search tool.

Oracle always talks about two type of refresh FAST and COMPLETE? FORCE is a refresh type where Oracle decides the best refreshing methods. Why do you leave Oracle to decide when you are the one who knows the most about your database?

A FORCE refresh is great for a system where there are frequent DML changes. What happens when you have to Truncate or drop Partition of a Master table? Fast refresh fails when you Truncate the table. In this situation, your fast refreshable Materialized view should be refresh Complete before you can use fast refresh.

This demo was done on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production. You [may] see different result on different version. I encourage you to test on your developing environment before moving to upper environments. Are you ready to fix fast refresh problem? Fix It Fassst.