The materialized view analysis feature provides you with the ability to view and manage materialized views within a tenant. On the Materialized View tab, you can view the basic information, refresh status, and refresh history of materialized views, helping you monitor their operational status and promptly identify and resolve any refresh exceptions.
Background information
A materialized view (MV) is a database object that stores the result set of a query. Unlike a regular view, which is a virtual table, an MV is a physical table that stores the result set of the query. When you create an MV, the database executes the associated SQL query and stores the result set on disk. This precomputed data reduces the need for real-time computation, thereby improving query performance and simplifying complex query logic. Materialized views are commonly used in scenarios that require fast report generation and data analysis. For more information about materialized views, see Overview of materialized views (MySQL mode) and Overview of materialized views (Oracle mode).
To ensure data consistency between the MV and the base table, the MV must be refreshed periodically. The materialized view analysis feature helps you:
- View the basic information and configurations of an MV.
- Monitor the refresh status and latency of an MV.
- View the refresh history of an MV.
- Quickly identify refresh failures or delays.
View the list of materialized views
Log in to the OceanBase Cloud console.
In the left-side navigation pane, click Instances.
In the instance list, find the target instance, click the instance name, and go to the Overview page of the instance.
In the left-side navigation pane, click Diagnostics, and then select the Real-Time Diagnostics tab on the Diagnostics page.
In the middle of the page, click the Materialized Views tab.
In the upper-left corner of the Materialized Views tab, select a tenant.
On the Materialized Views tab, click List to view the list of materialized views under the current tenant.
The following table describes the information in the list of materialized views.
Column |
Description |
|---|---|
| ID | The unique identifier of the materialized view. |
| Database | The name of the database to which the materialized view belongs. You can filter materialized views by database. |
| Name | The name of the materialized view. You can filter materialized views by name. |
| View Definition | The query definition of the materialized view. Click View to view the complete view definition statement. |
| Build Mode | The build mode of the materialized view. Valid values: • IMMEDIATE: The materialized view is immediately built. When you create a materialized view in this mode, the database executes the query and fills the data immediately. • DEFERRED: The materialized view is built on demand. When you create a materialized view in this mode, the database does not fill the data. You must manually trigger the build. |
| Refresh Mode | The refresh mode of the materialized view. Valid values: • ON COMMIT: The materialized view is automatically refreshed when the base table data is committed. • ON DEMAND: The materialized view is manually or periodically refreshed. • ON STATEMENT: The materialized view is refreshed each time the related statement is executed. |
| Refresh Method | The refresh method of the materialized view. Valid values: • COMPLETE: The materialized view is completely refreshed. The query statement is re-executed, and the original data is overwritten. • FAST: The materialized view is incrementally refreshed. Only the changed data is refreshed. The materialized view log (Mlog) is required. |
| Refresh Interval (s) | The refresh interval of the materialized view in automatic refresh mode, in seconds. |
| Current Latency (s) | The current data latency of the materialized view, in seconds. This value indicates the time elapsed since the last refresh. If the current latency is large, the data of the materialized view may be outdated. |
| Next Refresh | The time when the materialized view is scheduled to be refreshed next. |
| Last Refresh Time | The time when the materialized view was last refreshed. |
| Last Refresh Duration (s) | The time consumed in the last refresh of the materialized view, in seconds. |
Note
- You can filter materialized views by name to quickly locate the target materialized view.
- If the current latency of a materialized view is large, check whether the refresh task is running normally, or consider adjusting the refresh interval.
- The last refresh duration helps you evaluate the performance of the materialized view refresh. If the duration is too long, you may need to optimize the materialized view definition or refresh method.
View refresh records
The refresh records feature helps you track the historical refresh operations of materialized views, analyze the refresh frequency and performance trends, and promptly identify refresh anomalies.
On the Materialized Views tab, click Refresh Records.
On the refresh records page, you can use the following filters to find the target records:
- Only show timeout records: After you select this option, only refresh timeout records are displayed, which helps you quickly locate refresh anomalies.
- Show base table content: After you select this option, you can view the information about the base table of the materialized view.
- Time range: Select the time range of the refresh records that you want to view. You can:
- Click Custom to manually select the start and end times.
- Use the navigation arrows (
<and>) on either side of the time range selector to quickly adjust the time range.
View the historical refresh records of the materialized view in the refresh records list.
Column Name |
Description |
|---|---|
| ID | The unique identifier of the refresh record. |
| Database | The name of the database to which the materialized view belongs. You can filter the database. |
| Name | The name of the materialized view. |
| Refresh Method | The refresh method used in this refresh operation. Valid values: • COMPLETE: full refresh. • FAST: fast refresh (incremental refresh). |
| Refresh Time (s) | The time consumed by the refresh operation, in seconds. |
| Start Time | The start time of the refresh operation. |
| End Time | The end time of the refresh operation. |
| Result | The result of the refresh operation. Valid values: • Success: The refresh operation is completed. • Failed: The refresh operation fails. You can view the error information to troubleshoot the issue. |
| Rows Before Refresh | The number of data rows in the materialized view before the refresh operation. |
| Rows After Refresh | The number of data rows in the materialized view after the refresh operation. |
Note
- You can compare the number of data rows before and after the refresh operation to understand the impact of the refresh operation on the data.
- If the refresh operation takes a long time, we recommend that you check the complexity of the materialized view definition and the amount of data in the base table. Consider optimizing the refresh method or adjusting the refresh strategy.
- If the refresh operation fails, we recommend that you view the error information and check whether the base table data and materialized view logs (Mlogs) are normal.
- The refresh records list supports pagination. You can switch pages or adjust the number of records displayed per page by using the pagination controls at the bottom of the page.