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

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Working with database objects » Working with materialized views

 

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

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.

 To refresh a manual view (Sybase Central)
  1. Use the SQL Anywhere 12 plug-in to 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
 See also