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 (or manually refreshed) to keep the data up to date. Unlike regular views (virtual tables that are recalculated each time they are accessed), materialized views contain a physical copy of the data. This allows you to query the data directly from the materialized view without executing complex SQL query statements, significantly improving query performance by trading space for time.

Features

Types of materialized views

• Non-real-time materialized views

  A non-real-time materialized view stores results that are not the latest. It does not update immediately when the base table is updated, but rather refreshes at a scheduled time or upon manual trigger. When querying a non-real-time materialized view, only the physically stored data is queried. This approach is suitable for scenarios where data freshness is not a high priority, but query performance is crucial.

• 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 a mechanism similar to mlog, ensuring that the data in the materialized view reflects the latest state. During query execution, the real-time materialized view performs online calculations to integrate these changes, providing users with updated data. This way, even if the materialized view does not physically store the latest data, users still receive real-time query results.

Refresh strategies for materialized views

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

• Full refresh

  A full refresh is a straightforward method. Each time a refresh operation is executed, the system re-executes the query statement corresponding to the materialized view, recalculates the results, and completely overwrites the original view data. This strategy is suitable for scenarios with relatively small data volumes.

• Incremental refresh

  An incremental refresh only processes the data that has changed since the last refresh. To achieve accurate incremental refreshes, OceanBase implements a feature similar to Oracle's Materialized View Log (MLOG), which tracks incremental updates in the base table through logs. This allows for quick incremental refreshes of the materialized view. The incremental refresh strategy is particularly effective for large-scale data with frequent changes.

• Hybrid refresh

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

• No refresh

  The materialized view is only refreshed at creation and cannot be refreshed again afterward.

Refresh methods for materialized views

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

• Automatic refresh

  When creating a materialized view, you can configure the refresh timing and set an automatic refresh interval. You can use the START WITH datetime_expr and NEXT datetime_expr clauses to schedule the background automatic refresh of the materialized view.

• Manual refresh

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

Query acceleration with materialized views

• Query rewriting

  When creating a materialized view, you can specify ENABLE QUERY REWRITE to enable the automatic query rewriting feature. This allows the system to rewrite queries targeting the original table to queries targeting the materialized view, reducing the need for extensive business modifications.

• Index usage

  You can decide whether to create indexes on specific fields based on your business needs to speed up queries on those fields.

• Data storage format selection

  OceanBase Database supports both row-based and column-based materialized views, which can improve query performance in complex analytical scenarios involving materialized view references.

• Primary key usage

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

• Partitioning

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

Use cases

Materialized views are used to optimize query performance, especially when dealing with large volumes of data and complex queries. By precomputing and storing the results of view queries, they reduce the need for real-time calculations, simplify complex query logic, and are commonly used in scenarios such as rapid report generation and data analysis.

• Frequent queries for the same data: Materialized views are suitable for storing complex queries that are frequently repeated and resource-intensive, avoiding redundant calculations during each query and improving query efficiency.
• Data pre-aggregation: Aggregating sales data for daily, weekly, or monthly periods, as well as user behavior data, can be efficiently handled using materialized views. This is useful in scenarios like report generation and data analysis, where precomputed and stored aggregated data reduces the need for real-time calculations.
• Large-scale data analysis: For businesses with large data volumes, queries can be time-consuming. Materialized views help avoid extensive scans of the original data.
• Real-time analysis: For high-requirement real-time scenarios, real-time materialized views can accelerate queries to meet business needs.
• Multi-dimensional data analysis: Materialized views can precompute aggregated data for various dimension combinations, enabling fast multi-dimensional query responses.

Considerations

• Storage overhead: Materialized views consume additional storage space, so disk capacity must be considered.
• Refresh (maintenance) cost: Automatic or manual refreshes of materialized views consume system resources. If the base table data changes frequently, refresh operations may impact system performance.
• Data consistency and timeliness: The data in a materialized view may not be real-time, meaning it does not automatically update with changes in the original data. If the base table data changes, the view data may become outdated and require regular refreshes to maintain consistency.
• Design complexity: The design and creation of materialized views require careful consideration of expected query patterns and data access patterns to achieve optimal performance.

Base tables of materialized views

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

Limitations

• You cannot perform insert, delete, or alter operations on a materialized view.

• DDL operations on the base tables of a materialized view may prevent the materialized view from refreshing in the expected manner.

  • For a full refresh, the materialized view can refresh if the column types match those of the base table.
  • For an incremental refresh, you need to create a materialized view log (or enable the automatic management of materialized view logs) on the base table to enable incremental refreshes.

• Materialized views do not support the XML data type.

• Materialized views do not support table-level recovery.

• When you delete a materialized view, it does not go to the recycle bin. However, when you execute the drop database statement, the materialized view goes to the recycle bin along with the database.

• If you specify a primary key for a materialized view, data maintenance or updates on the materialized view will fail if the data does 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, an error will be returned if the c1 column in the t1 table contains null values during data maintenance.

Privileges

• You must have the CREATE TABLE privilege to create a materialized view.
• You must have the DROP TABLE privilege to drop a materialized view.
• You must have the SELECT privilege on all base tables to perform a full refresh.
• You must have the SELECT privilege on all base tables and on the materialized view log to perform an incremental refresh.
• You can only grant the SELECT privilege on a materialized view; other DML operations are not supported.

References

• Materialized view logs
• Create materialized views
• Refresh materialized views
• Materialized view query rewrite
• Query materialized views
• Delete materialized views