REFRESH MATERIALIZED VIEW view-list
 [ WITH { 
      ISOLATION LEVEL isolation-level
      | { EXCLUSIVE | SHARE } MODE } ]
 [ FORCE BUILD ] 

view-list :
[ owner.]materialized-view-name [, ... ]

isolation-level : 
READ UNCOMMITTED 
| READ COMMITTED 
| SERIALIZABLE 
| REPEATABLE READ 
| SNAPSHOT

Use this statement to initialize or refresh the materialized views listed in view-list.

If a REFRESH MATERIALIZED VIEW statement is executed against a materialized view that is not stale, a refresh is not performed unless the FORCE BUILD clause is specified.

The default refresh behavior for locking and data concurrency is as follows:

Several options need to have specific values for a REFRESH MATERIALIZED VIEW to succeed, and for the view to be used in optimization. Additionally, there are option settings that are stored for each materialized view when it is created. To refresh the view, or to use the view in optimization these option settings must match the current options.

When a refresh fails after having done partial work, the view is left in an uninitialized state, and the data cannot be restored to what it was before the refresh started. Examine the error that occurred when the refresh failed, resolve the issue that caused the failure, and execute the REFRESH MATERIALIZED VIEW statement again.

You can also use the IMMEDIATE REFRESH clause of the ALTER MATERIALIZED VIEW statement to change the view to be refreshed immediately when underlying data changes.

This statement cannot be executed when the connection has cursors opened with the WITH HOLD clause that use either statement or transaction snapshots.

Must have INSERT permission on the materialized view, and SELECT permission on the tables in the materialized view definition.

Any open cursors that reference the materialized view are closed.

A checkpoint is performed at the beginning of execution.

Automatic commits are performed at the beginning and end of execution.

While executing, an exclusive schema lock is placed on the materialized view being refreshed using the connection blocking option, and shared schema locks, without blocking, are placed on all tables referenced by the materialized view. If the WITH clause is specified, extra locks may be acquired on the underlying tables. Also, until refreshing is complete, the materialized view is in an uninitialized state, making it unavailable to the database server for either query optimization or query execution.

If the REFRESH MATERIALIZED VIEW statement executes using snapshot isolation, the database's transaction log will contain both the REFRESH statement text and copies of all of the individual rows that are inserted to the materialized view. The individual rows are necessary to ensure that, should the database require recovery, the contents of the view after recovery matches precisely the view's contents upon the original completion of the REFRESH MATERIALIZED VIEW statement. Moreover, the individual rows in the transaction log are applied individually when the database is mirrored. For this reason, you may wish to limit the frequency of REFRESH MATERIALIZED VIEW statements when using snapshot isolation, or truncate the transaction log periodically, using the BACKUP statement, to reduce the amount of disk space required for the transaction log.

• Materialized views
• CREATE MATERIALIZED VIEW statement
• Transactions and isolation levels
• Isolation levels and consistency
• ALTER MATERIALIZED VIEW statement
• Isolation levels and consistency
• blocking option
• Table locks
• Schema locks
• BACKUP statement
• allow_snapshot_isolation option
• Snapshot isolation
• Materialized views restrictions
• sa_refresh_materialized_views system procedure
• sa_materialized_view_info system procedure
• sa_materialized_view_can_be_immediate system procedure

Suppose you create a materialized view, EmployeeConfid99, and then populate it with data using the following statements:

CREATE MATERIALIZED VIEW EmployeeConfid99 AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid99;

Later, after the view has been in use, you want to refresh the view using the READ COMMITTED isolation level (isolation level 1), and you want the view to be rebuilt. You could execute the following statement:

REFRESH MATERIALIZED VIEW EmployeeConfid99
   WITH ISOLATION LEVEL READ COMMITTED
   FORCE BUILD;