There are three ways to categorize a materialized view's ability to be fast refreshed.
- It can never be fast refreshed.
- It can always be fast refreshed.
- 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.
drop materialized view mv ; drop materialized view log on t2 ;