Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Working with Database Objects » Working with views » Working with materialized views

Encrypting and decrypting materialized views Next Page

Enabling and disabling materialized views

You can control whether a materialized view is available for use by the database server by enabling or disabling it. A disabled materialized view is also not considered by the optimizer during optimization. If a query explicitly references a disabled materialized view, the query fails and an error is returned. When you disable a materialized view, the database server drops the data for the view, but keeps the definition in the database. When you re-enable a materialized view, it is in an uninitialized state and you must refresh it in order to populate it with data.

Non-materialized views that are dependent on a materialized view are automatically disabled by the database server if the materialized view is disabled. As a result, once you re-enable a materialized view, you must re-enable all dependent views. For this reason, you may want to determine the list of views dependent on the materialized view before disabling it. You can do this using the sa_dependent_views system procedure. This procedure examines the ISYSDEPENDENCY system table and returns the list of dependent views, if any.

When you disable a materialized view, all indexes for it are also dropped and must be recreated, if necessary, when the view is re-enabled.

To enable a materialized view (Sybase Central)

  1. Connect to the database as the DBA, or as owner of the materialized view.

  2. Open the Views folder for that database.

  3. Select the materialized view and then choose File > Recompile and Enable.

To disable a materialized view (Sybase Central)

  1. Connect to the database as the DBA, or as owner of the materialized view.

  2. Open the Views folder for that database.

  3. Select the materialized view and then choose File > Disable.

To enable a materialized view (SQL)

  1. Connect to the database as the DBA, or as owner of the materialized view.

  2. Execute an ALTER MATERIALIZED VIEW ... ENABLE statement.

  3. Execute a REFRESH MATERIALIZED VIEW to initialize the view and populate it with data.

To disable a materialized view (SQL)

  1. Connect to the database as the DBA, or as owner of the materialized view.

  2. Execute an ALTER MATERIALIZED VIEW ... DISABLE statement.

    3. Examples

    The following example disables the EmployeeConfidential materialized view. The data for the materialized view is dropped, the definition for the materialized remains in the database, the materialized view is unavailable for use by the database server, and dependent views, if any, are disabled.

    ALTER MATERIALIZED VIEW EmployeeConfidential DISABLE;

    The following two statements, respectively, re-enable the EmployeeConfidential materialized view, and then populate it with data.

    ALTER MATERIALIZED VIEW EmployeeConfidential ENABLE;
REFRESH MATERIALIZED VIEW EmployeeConfidential;
    See also