Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

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 the statement completes.

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

      For snapshot isolation, only the transaction snapshot level is supported by the REFRESH MATERIALIZED VIEW statement. Specify SNAPSHOT as the isolation level. The statement-snapshot and readonly-statement-snapshot levels are not supported.

    • 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.

    • 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.

  • 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 for 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.

Several options must 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.

Privileges

You must be the owner of the materialized view or have INSERT privilege on it. Additionally, you must be the owner of the underlying tables, or have SELECT privilege on them, or have the SELECT ANY TABLE system privilege.

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 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 want to limit the frequency of REFRESH MATERIALIZED VIEW statements when using snapshot isolation, or truncate the transaction log periodically, using the BACKUP DATABASE statement, to reduce the amount of disk space required for the transaction log.

Standards
  • ANSI/ISO SQL Standard

    Not in the standard.

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 GROUPO.Employees, GROUPO.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, drop the materialized view you created (DROP MATERIALIZED VIEW EmployeeConfid99). Otherwise, you cannot 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.