Overview

A materialized view is a special database object that stores the latest copy of query results and is periodically or manually refreshed. Unlike a standard view, which is a virtual table that requires calculations for each access, a materialized view contains a physical copy of the data. Therefore, you can directly query data from a materialized view without executing complex SQL statements. This greatly improves the query performance.

Materialized views are used to optimize the query performance of complex queries and queries dealing with large amounts of data. Materialized views can contain operations such as aggregation and join operations, and subqueries, and can be indexed and partitioned to further enhance the query performance.

Features

• Query performance improvement: Materialized views store query results. Therefore, you do not need to execute query statements for a query, which greatly improves the query performance.
• Data redundancy: Materialized views consume additional storage space to store query results.
• Automatic refresh: Materialized views can be automatically and periodically refreshed to keep data consistency with the base table.
• Support for complex queries: Materialized views store results of complex queries, such as queries involving aggregate functions, and grouping and join operations.

Scenarios

• Pre-summarization for frequently executed complex queries: Materialized views are suitable for storing summary data that is frequently calculated, such as monthly and quarterly summaries of sales data. You can use materialized views to avoid frequent calculations of large amounts of raw data.
• Optimization of common filtering conditions of queries: You can create materialized views to pre-filter data based on specific conditions. This improves the query efficiency.
• Acceleration of complex join operations: Materialized views are ideal for processing complex queries involving multiple join operations. You can save the results of complex queries in materialized views to speed up subsequent identical or similar queries.
• Clustering for performance improvement of grouping and aggregation queries: You can use materialized views to store clustered indexes and improve the efficiency of grouping and aggregation queries.

Considerations

• Storage consumption: Materialized views consume additional storage space. Therefore, you need to take the disk capacity into account.
• Refresh costs: The automatic or manual refreshes of materialized views consume system resources. If the data in the base tables changes frequently, refreshes of materialized views may affect the system performance.
• Data consistency and real-timeliness: Materialized views may not be consistent with base tables in real time. The data in a materialized view is not automatically updated along with the base table. If the base table data changes, the data in the materialized view may become outdated and needs to be refreshed periodically to maintain data consistency.
• Design complexity: When you design and create a materialized view, you must consider the expected query methods and data access methods to optimize the performance.

Limitations

• You cannot create a materialized view on normal or materialized views.

• You cannot perform INSERT, DELETE, or ALTER operations on a materialized view.

• You cannot rename a materialized view.

• DDL operations on the base table of a materialized view may cause refresh failures of the materialized view.

  • To perform a full refresh of a materialized view, the column types of the materialized view must match those of the base table.
  • To perform an incremental refresh of a materialized view, you must create a materialized view log on the base table. Some DDL operations on such base tables will be blocked.

• Materialized views do not support the XML data type.

• Materialized views do not support table-level restore.

• A materialized view that is separately dropped is not moved to the recycle bin. When you execute the DROP DATABASE statement to drop a database, materialized views in the database are moved to the recycle bin along with the database.

Operation privileges

• You need the CREATE TABLE privilege to create a materialized view.
• You need the DROP TABLE privilege to drop a materialized view.
• You need the SELECT privilege on all base tables to fully refresh a materialized view.
• You need the SELECT privilege on all base tables and the corresponding materialized view logs to perform an incremental refresh of a materialized view.
• You can grant only the SELECT privilege on a materialized view. Materialized views do not support other DML operations.

Base tables of materialized views

In a database, the base table of a materialized view is the original data table or view referenced to create the materialized view.

References

• Create a materialized view
• Query a materialized view
• Drop a materialized view
• Refresh a materialized view