Manual and immediate materialized views

There are two types of materialized views: manual and immediate, which implies the refresh type for the materialized view.

  • Manual views   A manual materialized view, or manual view, is a materialized view with a refresh type defined as MANUAL REFRESH. Data in manual views can become stale because manual views are not refreshed until a refresh is explicitly requested, for example by using the REFRESH MATERIALIZED VIEW statement or the sa_refresh_materialized_views system procedure. By default, when you create a materialized view, it is a manual view.

    A manual view is considered stale when any of the underlying tables change, even if the change does not impact data in the materialized view. You can determine whether a manual view is considered stale by examining the DataStatus value returned by the sa_materialized_view_info system procedure. If S is returned, the manual view is stale.

  • Immediate views   An immediate materialized view, or immediate view, is a materialized view with a refresh type defined as IMMEDIATE REFRESH. Data in an immediate view is automatically refreshed when changes to the underlying tables affect data in the view. If changes to the underlying tables do not impact data in the view, the view is not refreshed.

    Also, when an immediate view is refreshed, only the rows that need to be changed are acted upon. This is different from refreshing a manual view, where all data is dropped and recreated for a refresh.

You can change a manual view to an immediate view, and vice versa. However, the process for changing from a manual view to an immediate view has more steps. See Change a manual view to an immediate view.

Changing the refresh type for a materialized view can impact the status and properties of the view, especially when you change a manual view to an immediate view. See Materialized view statuses and properties.

Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0