Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Working with Database Objects » Working with views

Viewing system table data Next Page

Working with materialized views


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.

Considerations when using materialized views

You should carefully consider the following requirements and settings before using a materialized view:


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