Enable and disable 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 to populate it with data.

Regular 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, the data and indexes are dropped, and if the view was an immediate view, it is changed to a manual view. So, when you re-enable it, you'll need to refresh it, rebuild the indexes, and change it back to immediate view (if necessary).

You can grant permissions on disabled objects. Permissions to disabled objects are stored in the database and become effective when the object is enabled.

To disable a materialized view (Sybase Central)

Connect to the database as a user with DBA authority, or as owner of the materialized view.
In the left pane, double-click Views.
Right-click the materialized view and choose Disable.

To disable a materialized view (SQL)

Connect to the database as a user with DBA authority, or as owner of the materialized view.
Execute an ALTER MATERIALIZED VIEW ... DISABLE statement.

Example

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

CREATE MATERIALIZED VIEW EmployeeConfid55 AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid55;
ALTER MATERIALIZED VIEW EmployeeConfid55 DISABLE;

To enable a materialized view (Sybase Central)

Connect to the database as a user with DBA authority, or as owner of the materialized view.
In the left pane, double-click Views.
Right-click the materialized view and choose Recompile And Enable.
Optionally, right-click the view and choose Refresh Data to initialize the view and populate it with data.

To enable a materialized view (SQL)

Connect to the database as a user with DBA authority, or as owner of the materialized view.
Execute an ALTER MATERIALIZED VIEW ... ENABLE statement.
Optionally, execute a REFRESH MATERIALIZED VIEW to initialize the view and populate it with data.

Example

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

ALTER MATERIALIZED VIEW EmployeeConfid55 ENABLE;
REFRESH MATERIALIZED VIEW EmployeeConfid55;

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 Drop materialized views.