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, allowing for direct querying without the need for complex SQL statements. This significantly improves query performance by using space to speed up queries.

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 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 and provide updated data to users. 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 (also known as fast refresh), hybrid refresh, and never refresh. Here are the details:

• Full refresh

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

• Incremental refresh

  An incremental refresh only processes the changes that occurred since the last refresh. To achieve precise incremental refreshes, OceanBase implements a feature similar to Oracle's Materialized View Log (MLOG). This feature tracks incremental updates in the base table through detailed logs, enabling fast incremental refreshes for materialized views. 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.

• 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 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 use the DBMS_MVIEW.REFRESH package to manually refresh the materialized view, ensuring that its data remains 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 enhance performance.

• Query rewriting

  When creating a materialized view, you can specify ENABLE QUERY REWRITE to enable automatic query rewriting. In this case, the system can rewrite queries on the original table to queries on the materialized view, reducing the need for extensive business modifications.

• Using indexes

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

• Choosing 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.

• Using 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.

• Using partitions

  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.

Use cases

Materialized views are used to optimize query performance, especially when dealing with large volumes of data and complex queries. They precompute and store query results, reducing the need for real-time calculations and simplifying 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, or user behavior data, can be efficiently handled using materialized views. This is useful in scenarios like report generation and data analysis, where precomputed aggregated data can be stored and accessed without real-time computation.
• Large-scale data analysis: For businesses with large data volumes, queries can be time-consuming. Materialized views help by avoiding 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, enabling 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. If the base table data changes frequently, refresh operations may impact system performance.
• Data consistency and real-time updates: The data in a materialized view may not be real-time, meaning it does not automatically update with changes in the base table. 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 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 need to create a materialized view log on the base table (or enable the automatic management of materialized view logs) to perform an incremental refresh.

• 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 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 will fail. 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 will be returned when you maintain the materialized view data.

Privileges

• You need the CREATE TABLE privilege to create a materialized view.
• You need the DROP TABLE privilege to drop a materialized view.
• You need the SELECT privilege on all base tables to perform a full refresh.
• You need the SELECT privilege on all base tables and on the corresponding 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