Introduction

This note describes how to troubleshoot problems with a materialized view (or snapshot) concerning query rewrite and fast refresh. A materialized view must meet certain criteria to be fast refreshable. This note show how the so called capabilities can be showed.

Step 1: create the materialized view

The first step is to create the materialized view. If it cannot created with the fast refresh on commit option, just try the refresh on demand. At this stage, this is not important as this method will not look at the actual create-command but will look at all capabilities available for the materialized view.

In this example we will use the scott.emp table as base table for the materialized view. Initially we choose to create the materialized view with the ‘refresh on demand’-option. As there is no materialized view log at this moment, creating the materialized view with the ‘refresh fast on commit’ will fail.

create materialized view emp_mv
build immediate
refresh on demand
enable query rewrite
as
select * from scott.emp
/

Step 2: Create the explain table

The materialized view is explained in a table (like the plan table). This table (mv_capabilities_table) must be created in the current schema by running the following script:

start $ORACLE_HOME/rdbms/admin/utlxmv.sql

In this script, there is also an explanation of the showed values

Step 3: Explain the materialized view

The materialized view can be explained by executing the procedure dbms_mview.explain_mview. The only mandatory parameter is the name of the materialized view:

SQL> exec dbms_mview.explain_mview('EMP_MV') ;

PL/SQL procedure successfully completed.

The procedure doesn’t generate any output so it is not necessary to enable server output. Query the table mv_capabilities_table for more details about refreshing the materialized view:

select capability_name, possible from mv_capabilities_table
where mvname='EMP_MV'
and capability_name like 'REFRESH%'
/

CAPABILITY_NAME                P
------------------------------ -
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REFRESH_FAST_AFTER_INSERT      N
REFRESH_FAST_AFTER_ONETAB_DML  N
REFRESH_FAST_AFTER_ANY_DML     N
REFRESH_FAST_PCT               N

As showed in the result of the query, the materialized view is not fast refreshable. To make the materialized view capable of doing a fast refresh, a materialized view log on the table scott.emp must be created:

create materialized view log on scott.emp
with primary key including new values ;

Unfortunately Oracle doesn’t pick up the addition of the materialized view log on the scott.emp table. The best and quickest way to let Oracle notice the change is to re-create the materialized view (this time with refresh fast on commit). There is no create or replace option for a materialized view so it must be dropped first:

SQL> create materialized view emp_mv
   2 build immediate
   3 refresh fast on commit
   4 enable query rewrite
   5 as
   6 select * from scott.emp ;

Materialized view created.

Before the materialized view can be re-explained, the recommendation is to truncate the mv_capabilities_table table first:

SQL> truncate table mv_capabilities_table ;

Table truncated.

The capabilities of the materialized view must be explain again:

SQL> truncate table mv_capabilities_table ;

Table truncated.

SQL> select capability_name, possible from mv_capabilities_table
2 where mvname='EMP_MV'
3 and capability_name like 'REFRESH%'
4 /

CAPABILITY_NAME                P
------------------------------ -
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_AFTER_ANY_DML     Y
REFRESH_FAST_PCT               N

6 rows selected.

Now, the materialized view can do a fast refresh on commit depending on the materialized view log based on the scott.emp table

Sometimes it can be necessary to view the reason why a particular capability is not available. The table mv_capabilities_table had two important colums:

• RELATED_TEXT: information about tablenames or involved objects
• MSGTXT: Reason why the capability is available or not

SQL> select capability_name, possible, substr(related_text,1,8) as rel_text
2 ,substr(msgtxt,1,60) as msgtetxt
3 from mv_capabilities_table
4 /

CAPABILITY_NAME                P REL_TEXT MSGTETXT
------------------------------ - -------- ----------------------------------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REWRITE                        Y
PCT_TABLE                      N SCOTT.EM relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_AFTER_ANY_DML     Y
REFRESH_FAST_PCT               N          PCT is not possible on any of the detail tables in the mater
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N          general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE              N SCOTT.EM relation is not a partitioned table

14 rows selected.