Enable and disable optimizer use of a materialized view

The optimizer maintains a list of materialized views that can be used in the optimization process. A materialized view is not considered a candidate for use in optimization if its definition includes certain elements that the optimizer rejects, or if its data is not considered fresh enough to use. For information about what qualifies a materialized view as a candidate in the optimization process, see Improving performance with materialized views.

By default, materialized views are available for use by the optimizer. However, you can disable optimizer's use of a materialized view unless it is explicitly referenced in a query.

To determine if a materialized view is enabled or disabled for use by the optimizer, use the sa_materialized_view_info system procedure. See sa_materialized_view_info system procedure.

To enable a materialized view's use in optimization (Sybase Central)

Connect to the database as a user with DBA authority.
In the left pane, double-click Views.
Right-click the materialized view and choose Properties.
Click the General tab and select Used In Optimization.
Click OK.

To enable a materialized view's use in optimization (SQL)

Connect to the database as a user with DBA authority, or as owner of the materialized view.
Execute an ALTER MATERIALIZED VIEW statement with the ENABLE USE IN OPTIMIZATION clause.

Examples

The following statement enables the EmployeeConfid77 view for use in optimization:

ALTER MATERIALIZED VIEW GROUPO.EmployeeConfid77 ENABLE USE IN OPTIMIZATION;

To disable a materialized view's use in optimization (Sybase Central)

Connect to the database as a user with DBA authority.
In the left pane, double-click Views.
Right-click the materialized view and choose Properties.
Click the General tab and clear Used In Optimization.
Click OK.

To disable a materialized view's use in optimization (SQL)

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

Example

The following statement creates the EmployeeConfid77 materialized view, refreshes it, and then disables it for use in optimization.

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