Overview

A materialized view is a special type of view that stores a copy of the query results and is refreshed periodically (or manually) to keep the data up to date. Unlike regular views, which are virtual tables that recalculate data each time they are accessed, materialized views contain a physical copy of the data. This allows you to query 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 a result set that is not the latest. It does not update 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 approach 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 the mlog mechanism, ensuring that the data in the materialized view reflects the latest state. During queries, real-time materialized views perform 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 can 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 no refresh. Specifically:

• Full refresh

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

• Incremental refresh

  An incremental refresh processes only the changes since the last refresh. To achieve precise 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 materialized views can perform fast incremental refreshes. The incremental refresh method is particularly suitable for scenarios with large data volumes and frequent changes.

• Hybrid refresh

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

• No 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 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 refresh 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.

Accelerate query performance with materialized views

• Query rewriting

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

• Use indexes

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

• Choose data storage formats

  OceanBase Database supports both row-based and column-based storage formats for materialized views, which can improve query performance in complex analytical scenarios involving 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 scenarios based on the primary key.

• Use partitions

  When creating a materialized view, you can set table options and design and configure appropriate 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 when dealing with large amounts of data and complex queries. They precompute and store query results, reducing the need for real-time computation to enhance query performance and simplify complex query logic. They are commonly used in scenarios requiring quick 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, avoiding redundant calculations during each query to improve query efficiency.
• Data pre-aggregation: Aggregate sales data for daily, weekly, or monthly periods, or user behavior data, etc. Materialized views can precompute and store aggregated data for reports and data analysis, reducing the time required for real-time computation.
• Large-scale data analysis: For businesses with large data volumes, queries can be time-consuming. Materialized views can avoid scanning large amounts of original data.
• Real-time analysis: For businesses with high real-time requirements, real-time materialized views can accelerate queries to meet real-time 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) 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 real-time nature: 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, 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 optimization.

Base tables of 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 table 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 on the base table (or enable the automatic management of materialized view logs) before an incremental refresh can occur.

• Materialized views do not support the XML data type.

• Materialized views do not support table-level recovery.

• When a materialized view is deleted separately, it does not go to the recycle bin. However, when you execute the drop database statement, the database goes to the recycle bin.

• After you specify a primary key for a materialized view, if the data does not meet the primary key constraints during maintenance or update of the materialized view data, 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 the materialized view mv1, if the c1 column in the t1 table contains NULL values, an error is returned when you maintain the materialized view data.

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.
• For a full refresh, you must have the SELECT privilege on all base tables.
• For an incremental refresh, you must have the SELECT privilege on all base tables and the SELECT privilege on the materialized view log.
• You can only grant the SELECT privilege on a materialized view; other DML operations are not supported.

References

• Materialized Views Log
• Create Materialized Views
• Refresh Materialized Views
• Materialized Views Query Rewrite
• Query Materialized Views
• Delete Materialized Views