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
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.
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';
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|