Overview of materialized views

A materialized view is a special type of view that stores a copy of the query results and refreshes periodically (or manually) to keep the data up to date. Unlike regular views, which are virtual tables that require recalculating every 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 queries, significantly improving query performance by trading space for speed.

Features

Types of materialized views

• Non-real-time materialized views

  A non-real-time materialized view stores results that are not the latest. It is not updated immediately after the base table is updated. Instead, it is refreshed at a scheduled time or manually triggered. When querying a non-real-time materialized view, only the physically stored data is queried. This type of materialized view is suitable for scenarios where data freshness is not a high priority, but query performance is critical.

• 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 queries, the real-time materialized view integrates these changes through online computation, providing updated data to users. 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 (fast refresh), hybrid refresh, and never 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, calculates the results, and overwrites the existing view results. This strategy is suitable for scenarios with relatively small data volumes.

• Incremental refresh

  An incremental refresh processes only the changes that occurred since the last refresh. To achieve accurate incremental refreshes, OceanBase implements a feature similar to Oracle's MLOG (Materialized View Log), which tracks incremental updates in the base table through logs. This ensures that the materialized view can be quickly refreshed incrementally. The incremental refresh strategy is particularly suitable for scenarios with large data volumes and frequent changes.

• Hybrid refresh

  The system first attempts an incremental refresh. If it fails, a full refresh is executed.

• Never refresh

  A 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 the automatic refresh interval. You can specify 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 too long, you can use the DBMS_MVIEW.REFRESH package to manually refresh the materialized view, ensuring it stays synchronized with the base table data.

Accelerating queries with materialized views

Materialized views can include operations such as aggregation, joins, and subqueries, and can be indexed and partitioned to further improve performance.

• Query rewriting

  When creating a materialized view, you can specify ENABLE QUERY REWRITE to enable automatic rewriting capabilities. 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 to create indexes on specific fields based on your business needs to accelerate queries on those fields.

• Data storage format selection

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

• Primary key usage

  You can specify a primary key for a materialized view to optimize performance for 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 improve query performance and management efficiency.

Scenarios

Materialized views are used to optimize query performance, especially when dealing with large amounts of data and complex queries. They precompute and store the results of view queries, reducing the need for real-time computation to enhance query performance and simplify complex query logic. They 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 recalculated and resource-intensive. They avoid redundant calculations during each query, 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 can reduce real-time computation time.
• 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 businesses with high real-time requirements, real-time materialized views can accelerate queries to meet these needs.
• Multi-dimensional data analysis: Materialized views can precompute aggregated data for various dimension combinations, providing fast multi-dimensional query responses.

Considerations

• Storage overhead: Materialized views consume additional storage space, so disk capacity must be considered.
• Refresh (maintenance) costs: Automatic or manual refreshes of materialized views consume system resources. Frequent changes in the base table data may impact system performance.
• Data consistency and real-time updates: The data in a materialized view may not be real-time. It does not automatically update with changes in the base table data. If the base table data changes, the view data may become outdated, requiring regular refreshes to maintain data 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 for materialized views

In a database, the base table of a materialized view refers to the original table or view 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 must create a materialized view log (or enable the automatic management of materialized view logs) on the base table before an incremental refresh can occur.

• Materialized views do not support the XML data type.

• Materialized views do not support table-level recovery.

• When you delete a materialized view separately, 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.

• After you specify a primary key for a materialized view, if the data does not meet the primary key constraints during maintenance or updates, the view maintenance fails. For example, when you execute the CREATE MATERIALIZED VIEW mv1(PRIMARY KEY(c1)) AS SELECT c1 FROM t1; statement to create a materialized view named mv1, an error is returned if the c1 column in the t1 table contains NULL values.

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 corresponding materialized view logs to perform an incremental refresh.
• You can only grant the SELECT privilege to a materialized view. DML operations are not supported.

References

• Materialized view logs
• Create a materialized view
• Refresh a materialized view
• Query rewrite for materialized views
• Query a materialized view
• Delete a materialized view