Overview of materialized views

A materialized view is a special type of view that stores a copy of query results and is periodically refreshed (or manually refreshed) to keep the data up to date. Unlike a standard view (a virtual table that is recalculated each time it is accessed), a materialized view contains a physical copy of the data, which allows you to directly query the data from the materialized view without executing complex SQL statements, thus significantly improving query performance.

Features

Types of materialized views

• Non-real-time materialized views

  A non-real-time materialized view stores query results that are not necessarily up to date. It is not immediately refreshed when the base table is updated, but is refreshed at a scheduled time or manually. When you query a non-real-time materialized view, only the actual physical data is queried. This type of materialized view is suitable for scenarios where data freshness is not a major concern but query performance is important.

• Real-time materialized views

  A real-time materialized view is a database object that allows you to obtain real-time data. It captures and processes changes to the underlying base table by using the mlog mechanism to ensure that the data in the materialized view is up to date. When you query a real-time materialized view, the system performs online calculations to integrate the changes and display the updated data to you. Even if the materialized view does not store the latest data, you can still obtain real-time query results.

Refresh strategies

OceanBase Database supports four refresh strategies for materialized views: full refresh, quick refresh, hybrid refresh, and no refresh. The following table describes these strategies:

• Full refresh

  This strategy is a straightforward approach. When a refresh is performed, the system re-executes the query statement corresponding to the materialized view and recalculates and overwrites the original view result data. This strategy is suitable for scenarios with a small amount of data.

• Quick refresh

  This strategy processes only the data that has changed since the last refresh. To ensure precise quick refreshes, OceanBase Database implements a materialized view log (MLOG) feature similar to that of Oracle Database. This feature tracks and records incremental updates to the base table in detail in logs, ensuring that the materialized view can be quickly refreshed. This strategy is suitable for scenarios with a large amount of data and frequent changes.

• Hybrid refresh

  This strategy first attempts a quick refresh. If the quick refresh fails, a full refresh is performed.

• No refresh

  The materialized view is refreshed only when it is created and cannot be refreshed again.

Refresh methods

OceanBase Database supports automatic and manual refreshes for materialized views.

• Automatic refresh

  When you create a materialized view, you can specify the refresh timing and refresh interval. You can use the START WITH datetime_expr and NEXT datetime_expr clauses to specify the automatic refresh schedule for the materialized view in the background.

• Manual refresh

  If the materialized view is not configured for automatic refresh or the automatic refresh interval is large, you can use the DBMS_MVIEW.REFRESH package to manually refresh the materialized view to synchronize its data with the base table data.

Accelerate queries

• Query rewriting

  When you create a materialized view, you can specify the ENABLE QUERY REWRITE clause to enable automatic query rewriting for the materialized view. In this way, the system can rewrite queries for the original table into queries for the materialized view, reducing the amount of business modification.

• Use indexes

  You can create indexes on certain fields based on your business needs to accelerate queries on these fields.

• Choose a data storage format

  OceanBase Database (V4.3.0 to V4.3.2) supports only row-based materialized views, but from V4.3.3, it supports column-based materialized views. This allows you to obtain better query performance in some complex analytical scenarios that involve materialized view references.

• Use primary keys

  You can specify a primary key for a materialized view to optimize single-row lookups, range queries, and join operations based on the primary key.

• Use partitions

  When you create a materialized view, you can set table options and design and configure suitable partitioning options based on data characteristics and access patterns to improve query performance and management efficiency.

Scenarios

Materialized views are used to optimize query performance, especially in scenarios involving large amounts of data and complex queries. They precompute and store the query results of views to reduce real-time calculations and simplify complex query logic, and are commonly used in scenarios such as quick report generation and data analysis.

• Frequently query the same data: Materialized views are suitable for storing complex queries that are frequently executed and resource-intensive, avoiding repeated calculations each time you query the data and improving query efficiency.
• Pre-aggregate data: You can pre-aggregate sales data, user behavior data, and other data on a daily, weekly, or monthly basis for scenarios such as report generation and data analysis. This allows you to use materialized views to precompute and store aggregated data, reducing real-time calculations.
• Analyze large amounts of data: For businesses with a large amount of data, queries are usually time-consuming. You can use materialized views to avoid scanning the original data.
• Real-time analysis: For businesses that require high real-time performance, you can use real-time materialized views to accelerate queries and meet real-time business requirements.
• Multidimensional data analysis: You can precompute aggregate data in materialized views for various combinations of dimensions, and provide fast multidimensional query responses.

Considerations

• Storage overhead: Materialized views consume additional storage space, which requires you to consider disk capacity.
• Refresh (maintenance) costs: Automatic or manual refreshes of materialized views consume system resources. If the data in the base table changes frequently, refreshes may affect system performance.
• Data consistency and real-time performance: Data in materialized views may not be real-time. That is, data in materialized views is not automatically updated as the original data is updated. If the data in the base table changes, the data in the materialized view may become outdated, and you must periodically refresh the materialized view to maintain data consistency.
• Complexity: You must carefully consider expected query and data access patterns when you design and create materialized views to achieve optimal performance.

Base tables of materialized views

In a database, a base table of a materialized view is the original table or view referenced when the materialized view is created.

Limitations

• Inserting data, deleting data, or altering the definition of a materialized view is not allowed.

• Performing DDL operations on the base table of a materialized view may cause the materialized view to fail to refresh as expected.

  • For full refresh, as long as the column types between the materialized view and the base table match, the view can be refreshed.

  • For incremental refresh, you must create a materialized view log on the base table (or enable automatic management of materialized view logs) to perform incremental refresh.

    Note

    For V4.3.5, starting from V4.3.5 BP4, the automatic management feature for materialized view logs is supported. For more details, see Automatic management of materialized view logs.

• Materialized views do not support the XML type.

• Materialized views do not support table-level restore.

• When you drop a materialized view, it is not moved to the recycle bin. When you drop a database, the materialized view is moved to the recycle bin along with the database.

• If you specify a primary key for a materialized view, the view cannot be maintained or updated when the data does not meet the primary key constraint. For example, the following statement creates a materialized view mv1 with c1 as the primary key. If the c1 column contains null values in the t1 table, an error is returned when you maintain the data of the materialized view: CREATE MATERIALIZED VIEW mv1(PRIMARY KEY(c1)) AS SELECT c1 FROM t1;.

Privileges

• To create a materialized view, you must have the CREATE TABLE privilege.
• To drop a materialized view, you must have the DROP TABLE privilege.
• To perform a full refresh, you must have the SELECT privilege on all base tables.
• To perform a quick refresh, you must have the SELECT privilege on all base tables and the SELECT privilege on the corresponding mlogs.
• You can grant only the SELECT privilege on a materialized view. Other DML operations are not supported.

References

• Materialized view logs
• Create a materialized view
• Refresh a materialized view
• Query rewriting for materialized views
• Query a materialized view
• Drop a materialized view