|
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:
the database is large
frequent queries result in repetitive aggregation and join operations on large amounts of data
changes to underlying data are relatively infrequent
access to up-to-the-moment data is not a critical requirement
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:
the materialized view is enabled for use by the database server. See Enabling and disabling materialized views.
the materialized view is enabled for use in optimization. See Enabling and disabling optimizer use of a materialized view.
the materialized view has been initialized. See Initializing materialized views.
the materialized view meets all of the optimizer requirements for consideration. See Requirements for View Matching algorithm.
the values of some critical options used to create the materialized views match the options for the connection executing the query. See Restrictions when managing materialized views.
the last refresh of the materialized view does not exceed the staleness threshold set for the materialized_view_optimization database option. See Setting the optimizer staleness threshold for materialized views.
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.
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 when managing 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';
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 tracing level.
For more information on the enumeration phase of optimization, see Phases of query processing.
NoteWhen snapshot isolation is in use, the optimizer does not consider materialized views that were refreshed after the start of the snapshot for the current transaction. |
The optimizer includes a materialized view in the set of materialized views to be examined by the View Matching algorithm if the view definition:
contains only one query block
contains only one FROM clause
does not contain any of the following constructs or specifications:
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.
NoteThese restrictions only apply to the materialized views that are considered by the View Matching algorithm. If a materialized view is explicitly referenced in a query, the view is used by the optimizer as if it was a base table. |