Overview of materialized views

A materialized view is a special type of view that stores a copy of the query results and is periodically refreshed (manually or automatically) to keep the data up to date. Unlike regular views (virtual tables, which require recalculating data each time they are accessed), materialized views contain a physical copy of the data. This allows you to directly query data from materialized views without the need to execute complex SQL statements, significantly improving query performance by trading off space for time.

Features

Types of materialized views

• Non-real-time materialized views

  A non-real-time materialized view stores a result set that is not the latest. It does not update the materialized view immediately when the base table is updated. Instead, it refreshes the materialized view at a predetermined time or manually. When you query a non-real-time materialized view, only the data physically stored in the view is queried. This method is suitable for scenarios where data freshness is not a priority but query performance is important.

• Real-time materialized views

  A real-time materialized view is a database object that supports real-time data retrieval. It captures and processes changes in the underlying base table using mlogs to ensure that the data in the materialized view reflects the latest state. When a query is executed, the real-time materialized view integrates the changes through online computing to display the updated data. This way, even if the materialized view does not physically store the latest data, users can still obtain real-time query results.

Refresh strategies

OceanBase Database supports four refresh strategies for materialized views: complete refresh, incremental refresh (also known as fast refresh), hybrid refresh, and never refresh. Specifically:

• Complete refresh

  A complete refresh is a straightforward method where the system re-executes the query statement corresponding to the materialized view each time a refresh is performed. It recalculates and overwrites the original view results. This method is suitable for scenarios with relatively small amounts of data.

• Incremental refresh

  An incremental refresh only processes the data that has changed since the last refresh. To achieve precise incremental refreshes, OceanBase Database implements a materialized view log feature similar to Oracle MLOG. It tracks incremental updates to the base table through logs, ensuring that the materialized view can perform fast incremental refreshes. This method is particularly suitable for scenarios with large amounts of data and frequent changes.

• Hybrid refresh

  First, an incremental refresh is attempted. If it fails, a complete refresh is executed.

• Never refresh

  A materialized view that is set to never refresh is only refreshed when it is created and cannot be refreshed again after it is created.

Refresh methods

OceanBase Database supports automatic and manual refresh methods for materialized views.

• Automatic refresh

  When a materialized view is created, you can configure the refresh schedule and interval. You can use the START WITH datetime_expr and NEXT datetime_expr clauses to set the background automatic refresh schedule for the materialized view.

• Manual refresh

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

Accelerate queries with materialized views

Materialized views can contain operations such as aggregation, join, and subquery, and can be indexed and partitioned to further improve performance.

• Query rewriting

  When you create a materialized view, you can specify ENABLE QUERY REWRITE to enable the automatic rewriting capability of the materialized view. In this case, the system can rewrite queries on the original table to queries on the materialized view, reducing the amount of business modifications required.

• Use indexes

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

• Choose data storage formats

  OceanBase Database supports only row-based storage formats for materialized views in versions from V4.3.0 to V4.3.2. Starting from V4.3.3, it also supports columnar storage formats for materialized views, which can improve query performance in some complex analytical scenarios that involve materialized view references.

• Use primary keys

  You can specify a primary key for the materialized view to optimize performance for single-row lookups, range queries, or association scenarios based on the primary key.

• Use partitions

  When you create a materialized view, you can set table options and design and configure suitable partition 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 computing and improve query performance, simplifying complex query logic. They are commonly used in scenarios such as quick report generation and data analysis.

• Frequent queries for the same data: Materialized views are suitable for storing complex queries that frequently need to be recalculated and consume a lot of resources. This avoids recalculating the same data for each query, improving query efficiency.
• Data pre-aggregation: Summarize daily, weekly, or monthly sales data, or statistical user behavior data, such as in report generation and data analysis scenarios. You can use materialized views to precompute and store aggregated data, reducing the time spent on real-time computing.
• Analysis of large amounts of data: For businesses with a large amount of data, queries can be time-consuming. You can use materialized views to avoid extensive scanning of the original data.
• Real-time analysis: For businesses with high real-time requirements, you can use real-time materialized views to accelerate queries and meet the real-time requirements.
• Multi-dimensional data analysis: Use materialized views to precompute aggregated data for various dimension combinations, providing fast multi-dimensional query responses.

Considerations

• Storage overhead: A materialized view occupies additional storage space. You must ensure that the disk space is sufficient.
• Refresh (maintenance) costs: Automatic or manual refreshes of materialized views consume system resources. Frequent changes in the data of the base tables may affect system performance.
• Data consistency and real-time performance: The data in a materialized view may not be real-time. That is, the data in the materialized view does not automatically update when the original data changes. If the data in the base table changes, the data in the materialized view may become outdated. You must regularly refresh the materialized view to ensure data consistency.
• Design complexity: You must carefully consider the expected query and data access patterns when you design and create a materialized view to achieve optimal performance.

Base tables of a materialized view

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

Limitations

• You cannot insert, delete, or modify the definition of a materialized view.

• DDL operations performed on the base tables of a materialized view may result in the materialized view being unable to be refreshed in the expected mode.

  • For complete refreshes, the materialized view and base table must have matching column types.

  • For incremental refreshes, you must create materialized view logs on the base table (or enable the automatic management of materialized view logs) before you can perform incremental refreshes.

    Note

    For OceanBase Database V4.3.5, the automatic management of materialized view logs is supported since V4.3.5 BP4. For more information, see Automatic management of materialized view logs.

• Materialized views do not support the XML data type.

• Materialized views do not support table-level restore.

• When you delete a materialized view, it is not moved to the recycle bin. However, when you execute the DROP DATABASE statement, the database is moved to the recycle bin.

• If you specify a primary key for a materialized view, the data of the materialized view cannot contain values that do not meet the primary key constraints. For example, when you execute the CREATE MATERIALIZED VIEW mv1(PRIMARY KEY(c1)) AS SELECT c1 FROM t1; statement to create the materialized view mv1, if the c1 column in the t1 table contains NULL values, an error is reported during the creation of the materialized view.

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 complete refreshes, you must have the SELECT privilege on all base tables.
• To perform incremental refreshes, you must have the SELECT privilege on all base tables and the corresponding materialized view logs.
• You can grant only the SELECT privilege to a materialized view. Other DML operations are not supported.

References

• Materialized view logs
• Create a materialized view
• Refresh a materialized view
• Materialized view query rewriting
• Query a materialized view
• Drop a materialized view