Refreshing manual views

Manual views become stale when changes occur to their underlying base tables. Refreshing a manual view means that the database server re-executes the query definition for the view and replaces the view data with the new result set of the query. Refreshing makes the view data consistent with the underlying data. You should consider the acceptable degree of data staleness for the manual view and devise a refresh strategy accordingly. Your strategy should allow for the time it takes to complete a refresh, since the view is not available for querying during the refresh operation.

You can also set up a strategy in which the view is refreshed using events. For example, you can create an event to refresh at some regular interval.

Immediate views do not need to be refreshed except if they are uninitialized (contain no data), for example after being truncated.

To refresh a manual view (Sybase Central)

  1. Connect to the database as a user with DBA authority, or as a user with INSERT permission on the materialized view. You must also have SELECT permissions on the underlying tables.

  2. In the left pane, double-click Views.

  3. Right-click a materialized view and choose Refresh Data.

  4. Select an isolation level and click OK.

To refresh a manual view (SQL)

  1. Connect to the database as a user with DBA authority, or as a user with INSERT permission on the materialized view. You must also have SELECT permissions on the underlying tables.

  2. Execute a REFRESH MATERIALIZED VIEW statement.

Example

The following statement creates and then refreshes the EmployeeConfid33 materialized view.

CREATE MATERIALIZED VIEW EmployeeConfid33 AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid33;
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.

Related notes

You can also use the sa_refresh_materialized_views system procedure to refresh views. See sa_refresh_materialized_views system procedure.

You can configure a staleness threshold beyond which the optimizer should not use a materialized view when processing queries, using the materialized_view_optimization database option. See Setting the optimizer staleness threshold for materialized views.

When using the REFRESH MATERIALIZED VIEW statement, you can override the connection isolation level using the WITH ISOLATION LEVEL clause. For more information on how to control concurrency when refreshing a materialized view, see the WITH clause of the REFRESH MATERIALIZED VIEW statement.

Upgrading databases with materialized views

It is recommended that you refresh materialized views after upgrading your database server, or after rebuilding or upgrading your database to work with an upgraded database server.

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