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

SQL Anywhere 12.0.1 » SQL Anywhere Server - SQL Usage » Performance improvements, diagnostics, and monitoring » Tips for improving performance » 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.


DBA authority, or execute permissions on DBO owned procedures.

Context and remarks


 Retrieve the list of materialized view candidates
  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';


The list of candidate materialized views is displayed.



 See also