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)
  1. Use the SQL Anywhere 12 plug-in to 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 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.

 Examples
 To disable a materialized view's use in optimization (Sybase Central)
  1. Use the SQL Anywhere 12 plug-in to 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.

 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.

 Example
 See also