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:

For more information about isolation levels and on enabling snapshot isolation, see Isolation levels and consistency, and allow_snapshot_isolation option.

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. See Restrictions on materialized views.

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. See ALTER MATERIALIZED VIEW statement.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots. See Snapshot isolation.

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 or optimizer.

• Working with materialized views
• CREATE MATERIALIZED VIEW statement
• ALTER MATERIALIZED VIEW statement
• Isolation levels and consistency
• blocking option
• Table locks
• Schema locks
• 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;