Enabling and disabling 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 it is considered too stale for 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.

Sybase Central

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

  1. Connect to the database as a user with DBA authority.

  2. In the left pane, double-click Views.

  3. Right-click the materialized view and choose Properties.

  4. Click the General tab and select Used In Optimization.

  5. Click OK.

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

  1. Connect to the database as a user with DBA authority.

  2. In the left pane, double-click Views.

  3. Right-click the materialized view and choose Properties.

  4. Click the General tab and clear Used In Optimization.

  5. Click OK.

SQL statements

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

  1. Connect to the database as a user with DBA authority, or as owner of the materialized view.

  2. Execute an ALTER MATERIALIZED VIEW statement with the ENABLE USE IN OPTIMIZATION clause.

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

  1. Connect to the database as a user with DBA authority, or as owner of the materialized view.

  2. Execute an ALTER MATERIALIZED VIEW statement with the DISABLE USE IN OPTIMIZATION clause.

Examples

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 GROUPO.EmployeeConfid77 DISABLE USE IN OPTIMIZATION;

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

ALTER MATERIALIZED VIEW GROUPO.EmployeeConfid77 ENABLE USE IN OPTIMIZATION;
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 Dropping materialized views.

See also
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0