REFRESH MATERIALIZED VIEW statement

Initializes or refreshes the data in a materialized view by executing its query definition.

Syntax 
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
Parameters

  • WITH clause   Use the WITH clause to specify the type of locking to use on the underlying base tables during the refresh. The type of locking determines how the materialized view is populated and how concurrency for transactions is affected. The WITH clause setting does not impact the type of lock placed on the materialized view itself, which is always an exclusive lock. The possible locking clauses you can specify are:

    • ISOLATION LEVEL isolation-level   Use WITH ISOLATION LEVEL to change the isolation level for the execution of the refresh operation. The original isolation level is restored for the connection when statement execution finishes.

      For immediate views, isolation-level can only be SERIALIZABLE.

      For snapshot isolation, only snapshot level is supported (specify SNAPSHOT); statement-level and readonly-statement-snapshot are not supported.

      For information about isolation levels, see Using transactions and isolation levels, and Isolation levels and consistency.

    • EXCLUSIVE MODE   Use WITH EXCLUSIVE MODE if you do not want to change the isolation level, but want to guarantee that the data is updated to be consistent with committed data in the underlying tables. When using WITH EXCLUSIVE MODE, exclusive table locks are placed on all underlying base tables and no other transaction can execute queries, updates, or any other action against the underlying table(s) until the refresh operation is complete. If exclusive table locks cannot be obtained, the refresh operation fails and an error is returned. See Table locks.

    • SHARE MODE   Use WITH SHARE MODE to give read access on underlying tables to other transactions while the refresh operation takes place. When this clause is specified, shared table locks are obtained on all underlying base tables before the refresh operation is performed and until the refresh operation completes. See Table locks.

  • FORCE BUILD clause   By default, when you execute a REFRESH MATERIALIZED VIEW statement, the database server checks whether the materialized view is stale (that is, underlying tables have changed since the materialized view was last refreshed). If it is not stale, the refresh does not take place. Specify the FORCE BUILD clause to force a refresh of the materialized view regardless of whether the materialized view is stale.

Remarks

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 with respect to locking and data concurrency is as follows:

  • If the view is an immediate view, the default refresh behavior is WITH SHARE MODE, regardless of whether snapshot isolation is enabled.
  • If the view is a manual view and snapshot isolation is in use, the default is WITH ISOLATION LEVEL SNAPSHOT.
  • If the view is a manual view and snapshot isolation is not in use, the default is WITH SHARE MODE.

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

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. These option settings must match the current options in order to refresh the view, or to use the view in optimization. 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.

Permissions

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

Side effects

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.

See also
Standards and compatibility

  • SQL/2003   Vendor extension.

Example

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;
Caution

When you are done with this example, you should drop the materialized view you created. Otherwise, you will not be able to make schema changes to its underlying tables Employees and Departments, when trying out other examples. You cannot alter the schema of a table that has enabled, dependent materialized view. See Dropping materialized views.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0