SQL Snippets ™: Tutorials for Oracle Developers

In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode. Here is an example.

create materialized view log on t ;

create materialized view mv
  REFRESH FAST ON COMMIT
  as select * from t
;

select rowid, key, val from mv ;
 

ROWID                     KEY VAL
------------------ ---------- -----
AAAXNGAAEAAAAasAAA          1 a
AAAXNGAAEAAAAasAAB          2 b
AAAXNGAAEAAAAasAAC          3 c
AAAXNGAAEAAAAasAAD          4
 

Let's see what happens to the view in the course of an insert operation.

insert into t values ( 5, 'e' );

select rowid, key, val from mv ;
 

ROWID                     KEY VAL
------------------ ---------- -----
AAAXNGAAEAAAAasAAA          1 a
AAAXNGAAEAAAAasAAB          2 b
AAAXNGAAEAAAAasAAC          3 c
AAAXNGAAEAAAAasAAD          4
 

Nothing happend yet. Let's issue a COMMIT.

commit;

select rowid, key, val from mv ;
 

ROWID                     KEY VAL
------------------ ---------- -----
AAAXNGAAEAAAAasAAA          1 a
AAAXNGAAEAAAAasAAB          2 b
AAAXNGAAEAAAAasAAC          3 c
AAAXNGAAEAAAAasAAD          4
AAAXNGAAEAAAAatAAA          5 e
 

Note how the materialized view was automatically fast refreshed after the COMMIT command. No call to DBMS_MVIEW.REFRESH was required.

Restrictions

Materialized views can only refresh ON COMMIT in certain situations.

1. The materialized view cannot contain object types or Oracle-supplied types.
2. The base tables will never have any distributed transactions applied to them.

The first case produces an error during the CREATE MATERIALIZED VIEW command.

-- this materialized view is not fast refreshable
-- because the materialized view contains an Oracle-supplied type

create materialized view mv2
  REFRESH FAST ON COMMIT
  as select key, val, sys_xmlgen( val ) as val_xml from t
;
  as select key, val, sys_xmlgen( val ) as val_xml from t
                                                        *
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


 

The second case generates an error when a distributed transaction is attempted on the base table. In the following example materialized view MV (created at the top of this page) was created with REFRESH FAST. Attempting a distributed transaction on its base table, T, will therefore raise an error.

insert into t select key+10, val from T@REMOTE ;
commit;
commit
*
ERROR at line 1:
ORA-02050: transaction 5.21.5632 rolled back, some remote DBs may be in-doubt
ORA-02051: another session in same transaction failed


 

(REMOTE is a database link which loops back to the current account.)

ON DEMAND materialized views have no such restriction, as the following snippet demonstrates.

alter materialized view mv refresh ON DEMAND ;

insert into t select key+10, val from T@REMOTE ;
commit;

select * from t ;
 

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
         5 e
        11 a
        12 b
        13 c
        14
        15 e
 

-- cleanup test data in preparation for next section

delete from t where key >= 5 ;
commit ;
 

Gotcha

The SQL Language Reference manual says this about the ON COMMIT clause.

"Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view."
-- Oracle® Database SQL Language Reference: CREATE MATERIALIZED VIEW

When I first read this I assumed it meant that "REFRESH COMPLETE ON COMMIT" is not allowed. I also assumed that specifying "REFRESH ON COMMIT" is equivalent to specifying "REFRESH FAST ON COMMIT". The following examples prove neither is correct however.

create materialized view mv2
  REFRESH COMPLETE ON COMMIT
  as select key, val from t
;
 

As we can see the CREATE MATERIALZIED view command succeeded even though COMPLETE, not FAST, was specified with ON COMMIT. The next example examines the behavior of "REFRESH ON COMMIT" without a specified refresh method.

drop materialized view log on t ;

-- fast refreshable materialized views on T can no longer be created on T
-- because it has no materialized view log

drop materialized view mv2 ;

create materialized view mv2
  REFRESH ON COMMIT
  as select key, val from t
;
select rowid, key, val from mv2 ;
 

ROWID                     KEY VAL
------------------ ---------- -----
AAAXNMAAEAAAAakAAA          1 a
AAAXNMAAEAAAAakAAB          2 b
AAAXNMAAEAAAAakAAC          3 c
AAAXNMAAEAAAAakAAD          4
 

insert into t values ( 5, 'e' );
commit ;

select rowid, key, val from mv2 ;
 

ROWID                     KEY VAL
------------------ ---------- -----
AAAXNMAAEAAAAakAAE          1 a
AAAXNMAAEAAAAakAAF          2 b
AAAXNMAAEAAAAakAAG          3 c
AAAXNMAAEAAAAakAAH          4
AAAXNMAAEAAAAakAAI          5 e
 

The fact that all the rowid's in MV2 changed after the INSERT transaction committed confirms that a complete refresh took place during the commit. "REFRESH ON COMMIT" is not therefore equivalent to "REFRESH FAST ON COMMIT". In fact, when no REFRESH method is specified the default behaviour is "REFRESH FORCE" regardless of whether ON COMMIT is used or not.

Given these observations I can only conclude the documentation is either in error or misleading when it says "specify ON COMMIT to indicate that a fast refresh is to occur".

Cleanup

drop materialized view mv ;

drop materialized view mv2 ;

delete from t where key >= 5 ;
commit ;