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

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Working with database objects


Working with materialized views

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 stored in the catalog) and a table (it has persistent materialized rows). So, 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.

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, and where frequent queries result in repetitive aggregation and join operations on large amounts of data. For example, materialized views are ideal for use with data warehousing applications.

Materialized views are precomputed using data from the base tables that they refer to. Materialized views 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. See Optimizer estimates and column statistics.

While you can create indexes on materialized views, you cannot create keys, constraints, triggers, or articles on them.

 See also

Manual and immediate materialized views
How to view materialized view information in the database
When to use materialized views
Materialized view statuses and properties
Restrictions on materialized views
Create materialized views
Initialize materialized views
Refresh manual views
Change a manual view to an immediate view
Encrypt and decrypt materialized views
Enable and disable materialized views
Enable and disable optimizer use of a materialized view
Setting the optimizer staleness threshold for materialized views
Hide materialized views
Drop materialized views