Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Performance improvements, diagnostics, and monitoring » Tips for improving performance » Tip: Use materialized views to improve query performance

 

Retrieving the list of materialized view candidates

From Interactive SQL, you can retrieve a list of materialized views that are candidates to be considered by the optimizer.

Prerequisites

There are no prerequisites for this task.

 Task
  1. Execute the following statement:

    SELECT * FROM sa_materialized_view_info( ) WHERE AvailForOptimization='Y';

    The list returned is specific to the requesting connection, since the optimizer takes into account option settings when generating the list. A materialized view is not considered a candidate if there is a mismatch between the options specified for the connection and the options that were in place when the materialized view was created.

  2. To obtain a list of all materialized views that are not considered candidates for the connection because of a mismatch in option settings, execute the following from the connection that will execute the query:

    SELECT * FROM sa_materialized_view_info( ) WHERE AvailForOptimization='O';

Results

The list of candidate materialized views is displayed.

 See also