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 must 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)

 To disable a materialized view (SQL)

 To enable a materialized view (Sybase Central)

 To enable a materialized view (SQL)

 Example of disabling a materialized view

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;

 Example of enabling a materialized view

ALTER MATERIALIZED VIEW EmployeeConfid55 ENABLE;
REFRESH MATERIALIZED VIEW EmployeeConfid55;

 See also