A materialized view is a view whose result set is stored on disk, much like a base table, but that is computed, much like a view. Conceptually, a materialized view is both a view (it has a query specification) and a table (it has persistent materialized rows). Consequently, many operations that you perform on tables can be performed on materialized views as well. For example, you can build indexes on, and unload from, materialized views.

In designing your application, consider defining materialized views for frequently-executed expensive queries or expensive parts of your queries, such as those involving intensive aggregation and join operations. Materialized views are designed to improve performance in environments where:

You do not have to change your queries to benefit from materialized views. For example, materialized views are ideal for use with data warehousing applications where the underlying data doesn’t change very often.

The optimizer maintains a list of materialized views to consider as candidates for partially or fully satisfying a submitted query when optimizing. If the optimizer finds a candidate materialized view that can satisfy all or part of the query, it includes the view in the recommendations it makes for the enumeration phase of optimization, where the best plan is determined based on cost. The process used by the optimizer to match materialized views to queries is called view matching. Before a materialized view can be considered by the optimizer, the view must satisfy certain conditions. This means that unless a materialized view is explicitly referenced by the query, there is no guarantee that it will be used by the optimizer. You can, however, make sure that the conditions are met for the view to be considered.

If the optimizer determines that materialized view usage is allowed, then each candidate materialized view is examined. A materialized view is considered for use by the View Matching algorithm if:

If the materialized view meets the above criteria, and it is found to satisfy all or part of the query, the View Matching algorithm includes the materialized view in its recommendations for the enumeration phase of optimization, when the best plan is found based on cost. However, this does not mean that the materialized view will ultimately be used in the final execution plan. For example, materialized views that appear suitable for computing the result of a query may still not be used if another access plan, which doesn't use the materialized view, is estimated to be cheaper.

Determining the list of materialized view candidates for the current connection

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

Determining whether a materialized view was considered by the optimizer

You can see the list of materialized views used for a particular query by looking at the Advanced Details window of the query's graphical plan in Interactive SQL. See Reading execution plans.

You can also use Application Profiling mode in Sybase Central to determine whether a materialized view was considered during the enumeration phase of a query, by looking at the access plans enumerated by the optimizer. Tracing must be turned on, and must be configured to include the OPTIMIZATION_LOGGING tracing type, in order to see the access plans enumerated by the optimizer. For more information about this tracing type, see Application profiling, and Choosing a diagnostic tracing level.

For more information about the enumeration phase of optimization, see Phases of query processing.

Requirements for View Matching algorithm

The optimizer includes a materialized view in the set of materialized views to be examined by the View Matching algorithm if the view definition:

The materialized view definition may contain a GROUP BY clause, as well as a HAVING clause, provided the HAVING clause does not contain subselects or subqueries.

See also

• Reading execution plans
• Phases of query processing
• Application profiling