At any given time, you can obtain a list of all materialized views that are candidates to be considered by the optimizer, by executing the following command:

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. For a list of the options that must match, see Restrictions on materialized views.

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';