Consider using materialized views for frequently executed expensive queries, such as those involving intensive aggregation and join operations. Materialized views provide a queryable structure in which to store aggregated, joined data. 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, and access to up-to-the-moment data is not a critical requirement. For example, materialized views are ideal for use with data warehousing applications.
A materialized view is a view whose result set has been computed and stored on disk, similar to a base table. 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.
Materialized views get their data from the execution of the underlying queries and are read only; no data-altering operations such as INSERT, LOAD, DELETE, and UPDATE can be used on them.
Column statistics are generated and maintained for materialized views in exactly the same manner as for tables. For more information about Column statistics, see Optimizer estimates and column statistics.
While you can create indexes on materialized views, you cannot create keys, constraints, triggers, or articles on them.
You should carefully consider the following requirements and settings before using a materialized view:
Disk space requirements Since materialized views contain a duplicate of data from base tables, you may need to allocate additional space to accommodate the materialized views you create. Careful consideration needs to be given to the additional space requirements so that the benefit derived is balanced against the cost of using materialized views.
Maintenance costs and data concurrency requirements The data in materialized views needs to be periodically refreshed. The frequency at which a materialized view needs to be refreshed needs to be determined by taking into account potentially conflicting factors such as:
Rate at which underlying data changes Frequently changing data renders the data in materialized views obsolete soon after the view is refreshed.
Cost of refreshing Depending on the complexity of the underlying query, and the amount of data involved, the computation of a materialized view may be very expensive. Frequently refreshing such a view may impose an unacceptable level of load on the database server.
Data concurrency requirements of applications The use of materialized views by the database server to answer queries means that the database server can present stale data to applications. Stale data is data that no longer represents the current state of the database. The degree of staleness is governed by the frequency at which the materialized view is refreshed. An application must be designed to determine the degree of staleness it can tolerate to achieve improved performance. For more information on managing data staleness in materialized views, see Setting the optimizer staleness threshold for materialized views.
Data consistency requirements When refreshing materialized views, you must determine the consistency with which the materialized view should be refreshed. See the WITH ISOLATION LEVEL clause of the REFRESH MATERIALIZED VIEW statement.
Use in optimization You should verify that the optimizer considers the materialized views when executing a query. 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. See Application profiling. Tracing must be turned on, and must be configured to include the OPTIMIZATION_LOGGING tracing type, to see the access plans enumerated by the optimizer. For more information about this tracing type, see Choosing a tracing level. For more information on how the optimizer uses materialized views, see Improving performance with materialized views.
Restrictions when managing materialized views
Creating materialized views
Initializing materialized views
Refreshing materialized views
Encrypting and decrypting materialized views
Enabling and disabling materialized views
Enabling and disabling optimizer use of a materialized view
Setting the optimizer staleness threshold for materialized views
Hiding materialized views
Dropping materialized views
Retrieving information about materialized views