Overview
Unlike conventional views, materialized views store the data of query results. When you create a materialized view, the database executes the SQL query associated with the view and stores the result set on the disk. This way, when you query data from the materialized view, the database does not need to execute a complex SQL query to recalculate the data, because the data is pre-calculated and stored.
Benefits
Improved performance
Materialized views store pre-calculated data. Therefore, the calculation does not need to be repeated during queries. This significantly improves the efficiency of data retrieval, especially in complex aggregations and joins.
Reduced usage of computing resources
Calculation in complex queries consumes a large amount of CPU and memory resources. Materialized views reduce the load of OBServer nodes by avoiding repeated calculation.
Data consistency
Materialized views can be designed to be regularly refreshed to keep their data up-to-date. In addition, a consistent data view is provided to facilitate reporting and data analysis.
Enhanced data access
Materialized views can function as the data access layer to support complex business logic. This way, you can access preprocessed data without understanding the complex SQL logic.
Application scenarios
Optimize OLAP operations
Materialized views can greatly improve the query performance of online analytical processing (OLAP) operations, especially data aggregations and multidimensional analysis.
Cache compute-intensive query results
Materialized views can cache the results of queries that consume a large amount of computing resources for future access.
Reduce the load of the primary database
You can create materialized views on read replicas so that the read replicas take over the load of complex queries from the primary database. This is particularly helpful in distributed database systems.
However, materialized views occupy additional storage space, and you must manage update strategies for them to keep their data up-to-date. Therefore, you must consider both the performance improvement and the maintenance costs before you choose to use materialized views. Proper use of materialized views can greatly improve the performance and efficiency of database systems.
Limitations
- A materialized view does not support table-level restore.
- When you separately drop a materialized view, it will not be moved to the recycle bin. When you drop the database, the materialized view will be moved to the recycle bin with the database.