Overview
Unlike traditional views, materialized views actually store the data of query results. In other words, when you create a materialized view, the database executes the associated SQL query and stores the result set on the disk. Materialized views precompute and store the query results, reducing real-time calculations and improving query performance. They are also used in scenarios for generating quick reports and analyzing data.
This topic introduces the principles of materialized views, including the full refresh and incremental refresh mechanisms, and their differences in data updates. It also covers the principles of real-time materialized views and how they ensure timeliness, real-time query results, and accuracy.
Full refresh materialized views
The full refresh strategy clears all existing data from the materialized view and reinserts the latest query result set. The data of hidden container tables cleared during each refresh is exchanged with the container tables when new query result sets are successfully inserted into the hidden container tables. The query result set of the materialized view after the exchange is the refreshed result set.
Incremental refresh materialized views
Incremental refreshes depend on materialized view logs (Mlogs). An Mlog is a structure for tracking data changes in the base table (the table referenced by the materialized view) since the last refresh. It records all changes, including insertions, updates, and deletions, in the base table.
Mlogs record only changed data, which allows for quick incremental refreshes of materialized views and ensures consistency between the materialized views and the base tables.
The incremental refresh strategy is more efficient. It processes only data that has changed since the last update and applies the differences to the materialized views. All incremental data changes are recorded in the Mlogs, detailing the old and new values of all modified rows, ensuring data accuracy and timeliness.
As shown in the preceding figure, when DML operations (such as INSERT and UPDATE) are performed on the base table, the DAS (Database Activity Stream) mechanism ensures that the base table and the Mlogs are updated simultaneously. Periodic background maintenance tasks apply incremental changes in the Mlogs to the materialized views. Data that has been refreshed from the Mlogs is deleted by the deletion module. At this time, queries access the latest data in the materialized views and the data in the Mlogs that has not been refreshed, and return the merged latest result set to users.
Principles of real-time materialized views
OceanBase Database supports real-time materialized views. The difference between real-time materialized views and non-real-time materialized views is that non-real-time materialized views query data only from MViews, whereas real-time materialized views query data from both MViews and changes in the base tables recorded in Mlogs. Real-time materialized views can return real-time query results by online computing data in MViews and Mlogs when queries are executed.
Real-time materialized views are closely related to incremental refreshes. Real-time materialized views can quickly capture the latest changes in the base tables by continuously querying Mlogs, which keeps the materialized views up to date.