SQL Snippets ™: Tutorials for Oracle Developers

There are three ways to categorize a materialized view's ability to be fast refreshed.

1. It can never be fast refreshed.
2. It can always be fast refreshed.
3. It can be fast refreshed after certain kinds of changes to the base table but not others.

For the first case Oracle will raise an error if you try to create such a materialized view with its refresh method defaulted to REFRESH FAST. In the example below table T does not have a materialized view log on it. Materialized views based on T cannot therefore be fast refreshed. If we attempt to create such a materialized view we get an error.

create materialized view MV
  REFRESH FAST
  as select * from t2
;
  as select * from t2
                   *
ERROR at line 3:
ORA-23413: table "SCOTT"."T2" does not have a materialized view log


 

For the second case materialized views are created without error, obviously, and will always be fast refreshed unless a complete refresh is explicitly requested. The third case is a little trickier. The next example demonstrates why.

select * from t2 ;
 

       KEY      T_KEY        AMT
---------- ---------- ----------
        10          1        100
        20          1        300
        30          1        200
        40          2        250
        50          2        150
 

create materialized view log on t2
  with primary key, rowid, sequence ( t_key, amt )
  including new values
;

create materialized view mv
  REFRESH FAST
  as
    select t_key, max( amt ) amt_max
    from t2
    group by t_key
;

select rowid, t_key, amt_max from mv ;
 

ROWID                   T_KEY    AMT_MAX
------------------ ---------- ----------
AAAhMzAAEAAAEG8AAA          1        300
AAAhMzAAEAAAEG8AAB          2        250
 

So far everything works as expected. We created a materialized view log and created a materialized view with fast refresh as its default refresh method. Let's try inserting a row into the base table.

insert into t2 values ( 5, 2, 500 );
commit;

execute dbms_mview.refresh( list => 'MV', method => 'F' );

select rowid, t_key, amt_max from mv ;
 

ROWID                   T_KEY    AMT_MAX
------------------ ---------- ----------
AAAhMzAAEAAAEG8AAA          1        300
AAAhMzAAEAAAEG8AAB          2        500
 

Again, it worked as expected. The view was fast refreshed (the rowid's did not change after the DBMS_MVIEW.REFRESH command) and the materialized view correctly shows 500 as the maximum value for rows with T_KEY = 2. Now let's try deleting a row from the base table.

delete from t2 where key = 5 ;
commit;

execute dbms_mview.refresh( list => 'MV', method => 'F' );
BEGIN dbms_mview.refresh( list => 'MV', method => 'F' ); END;

*
ERROR at line 1:
ORA-32314: REFRESH FAST of "SCOTT"."MV" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


 

This time we received an error when we attempted a fast refresh. The reason is because this type of materialized view is an "insert-only" materialized view, i.e. it is only fast refreshable for inserts and direct loads, not updates or deletes. (We will see why it is an insert-only view in the next topic, DBMS_MVIEW.EXPLAIN_MVIEW.) To synchronize an insert-only materialized view after a delete we need to do a complete refresh.

execute dbms_mview.refresh( list => 'MV', method => 'C' );

select rowid, t_key, amt_max from mv ;
 

ROWID                   T_KEY    AMT_MAX
------------------ ---------- ----------
AAAhMzAAEAAAEG8AAC          1        300
AAAhMzAAEAAAEG8AAD          2        250
 

Restrictions on Fast Refresh

So how do we know whether a materialized view can be fast refreshed each time, sometimes, or never? One way would be to learn all the documented restrictions for fast refreshable materialized views. Here are some of them.

In general materialized views cannot be fast refreshed if the base tables do not have materialized view logs or the defining query:

• contains an analytic function
• contains non-repeating expressions like SYSDATE or ROWNUM
• contains RAW or LONG RAW data types
• contains a subquery in the SELECT clause
• contains a MODEL clause
• contains a HAVING clause
• contains nested queries with ANY, ALL, or NOT EXISTS
• contains a CONNECT BY clause
• references remote tables in different databases
• references remote tables in a single database and defaults to the ON COMMIT refresh mode
• references other materialized views which are not join or aggregate materialized views.

There are even more restrictions for materialized views containing joins, aggregates, UNION ALL, subqueries, etc. They are documented in various sections of a few different manuals and are too numerous and complex to repeat here. The following links can help you find them if required though.

• CREATE MATERIALIZED VIEW - FAST Clause
• General Restrictions on Fast Refresh
• Restrictions on Fast Refresh on Materialized Views with Joins Only
• Restrictions on Fast Refresh on Materialized Views with Aggregates
• Restrictions on Fast Refresh on Materialized Views with UNION ALL
• Restrictions for Materialized Views with Subqueries
• Restrictions for Materialized Views with Unions Containing Subqueries
• Restrictions for Using Multitier Materialized Views
• Restrictions for Materialized Views with Collection Columns

Fortunately there is a second, simpler alternative for determining whether a materialized view is fast refreshable or not. It uses the DBMS_MVIEW.EXPLAIN_MVIEW utility which we will explore next.

Cleanup

drop materialized view mv ;

drop materialized view log on t2 ;