Overview
Materialized views differ from traditional views because they actually store the results of the query. In short, when you create a materialized view, the database executes the associated SQL query and stores the result set on disk. By precomputing and storing the query results, materialized views reduce the need for real-time computation, thereby improving query performance and simplifying complex query logic. They are commonly used in scenarios such as rapid report generation and data analysis.
This document will introduce the principles of materialized views, including the mechanisms of full refresh and incremental refresh, and how these two refresh methods differ in data updates. It will also explain the principles of real-time materialized views and how they ensure the timeliness, real-time, and accuracy of updates and query results.
Principles of Full Refresh for Materialized Views
The full refresh strategy clears all existing data in the materialized view and reinserts the latest query result set during each update. During each refresh, the data in the hidden container table is cleared, and when the hidden container table successfully inserts the new query result set, it swaps with the container table. Only after the swap is successful will the query results of the materialized view reflect the refreshed data.
Principles of Incremental Refresh for Materialized Views
Incremental refresh relies on materialized view logs. A materialized view log is a structure used to track changes in the base table (the table referenced by the materialized view) and records all changes that have occurred since the last refresh, including insertions, updates, and deletions.
Materialized view logs only process changed data, record the changes, and perform quick incremental updates to the materialized view, ensuring that the materialized view remains consistent with the base table data.
The incremental refresh strategy is more efficient, as it only processes data that has changed since the last update and applies these changes to the materialized view. All incremental data changes are recorded in the materialized view log, which lists the old and new values of all modified rows, ensuring the accuracy and timeliness of the data.
As shown in the figure above, when DML operations (such as INSERT and UPDATE) are performed on the base table, the DAS mechanism ensures that both the base table and the materialized view log are updated simultaneously. Background maintenance tasks periodically apply the incremental changes from the materialized view log to the materialized view. Once some data in the materialized view log is refreshed to the materialized view, it is cleared by the deletion module. At this point, query operations will access both the materialized view and the unrefreshed data in the materialized view log, merge the information, and return the latest result set to the user.
Implementation principle of real-time materialized views
OceanBase Database supports real-time materialized views. The difference between a real-time materialized view and a non-real-time materialized view is that a non-real-time materialized view queries data only from the materialized view (MView), whereas a real-time materialized view queries data from both the MView and the base tables. The changes in the base tables are recorded in the materialized view log (Mlog). During query execution, a real-time materialized view can calculate data in the MView and Mlog online, thereby returning real-time materialized view results.
Real-time materialized views and incremental refreshes are closely related. Real-time materialized views can quickly capture the latest changes in the base tables by continuously querying the Mlogs, thereby enabling the materialized views to be updated in real time.