Overview
Materialized views differ from regular 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 that require fast report generation and data analysis.
This document will explain the principles of materialized views, including the mechanisms for full refresh and incremental refresh, and how these two methods differ in handling data updates. It will also introduce the principles of real-time materialized views and how they ensure the timeliness, real-time nature, 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. When a full refresh is performed, the hidden container table's data is cleared, and the new query result set is inserted into the hidden container table. After the hidden container table successfully inserts the new data, 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) since the last refresh. It records all changes, including inserts, updates, and deletes, that have occurred in the base table since the last refresh.
Materialized view logs only handle changed data, record these changes, and quickly update the materialized view incrementally, ensuring data consistency between the materialized view and the base table.
The incremental refresh strategy is more efficient because 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 data accuracy and timeliness.
As shown in the figure above, when a DML operation (such as INSERT or UPDATE) is 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 part of the 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 this information, and return the latest result set to the user.
How real-time materialized views work
OceanBase Database supports real-time materialized views. The difference between real-time and non-real-time materialized views is that non-real-time materialized views only query data from the materialized view (MView), while real-time materialized views also access changes to the base table data. These changes are recorded in the materialized view log (Mlog). During query execution, real-time materialized views can calculate data from both the MView and Mlog in real time, returning up-to-date results.
Real-time materialized views are closely related to incremental refreshes. By continuously querying the materialized view log, real-time materialized views can quickly capture the latest changes in the base table data, keeping the materialized view updated in real time.