|
![READTEXT statement [T-SQL]](gif/backward.gif)
Initializes or refreshes the data in a materialized view by executing its query definition.
REFRESH MATERIALIZED VIEW [ owner.]materialized-view-name
[ WITH { ISOLATION LEVEL isolation-level | EXCLUSIVE MODE } ]
[ FORCE BUILD ]
isolation-level :
0 | 1 | 2 | 3 | snapshot | statement-snapshot | readonly-statement-snapshot
WITH ISOLATION LEVEL isolation-level clause Use this clause to change the isolation level for the execution of the refresh operation. For information on isolation levels, see Using Transactions and Isolation Levels, and Isolation levels and consistency.
WITH EXCLUSIVE MODE clause Use this clause if you do not want to change the isolation level, but do want to guarantee that the data is updated to be consistent with committed data in the underlying tables. When using WITH EXCLUSIVE MODE, table locks are placed on all tables referenced by the materialized view to prevent data in those tables from changing while the materialized view is refreshed. However, other connections can still read data from the underlying tables. If table locks cannot be obtained, the refresh operation fails and an error is returned.
FORCE BUILD clause By default, when you execute the REFRESH MATERIALIZED VIEW statement, the database server checks whether the underlying data has changed. If it hasn't, the materialized view is not refreshed. Specify FORCE BUILD to refresh the materialized view, regardless of whether underlying data has changed.
Use this statement to initialize a materialized view, or to refresh data in a materialized view. Refreshing means that the database re-executes the query definition for the view, and replaces the materialized view data with the new data that is returned, thereby making the materialized view data consistent with the data in the underlying tables. By default, the database server refreshes the materialized view using the current isolation level set for the connection.
Several options need to have specific values in order to refresh a materialized view, and in order for the view to be used in optimization. Additionally, there are options that are remembered for each materialized view; these options must match the current options in order to refresh the view, or to use the view in optimization. For more information about the options that must have specific settings, see Restrictions when managing materialized views.
Must have INSERT permission on the materialized view, and SELECT permission on the tables in the materialized view definition.
Not supported within snapshot transactions. See Snapshot isolation.
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 lock is placed on the materialized view being refreshed using the connection BLOCKING option, and shared table locks, without blocking, are placed on all tables referenced by the materialized view. Also, until refreshing is complete, the materialized view is in an uninitialized state, making it unavailable to the database server or optimizer.
SQL/2003 Vendor extension.
The following statement changes the isolation level of the connection to 1 (read committed), and then refreshes the data in the ProductIDsPerCustomer materialized view by forcing the view to be rebuilt:
REFRESH MATERIALIZED VIEW ProductIDsPerCustomer WITH ISOLATION LEVEL 1 FORCE BUILD;
The original isolation level is restored at the end of the statement execution.