A materialized view is a special type of view that stores a copy of query results and is periodically refreshed (or can be refreshed manually) to maintain the data in its latest state. Unlike a regular view (a virtual table that needs to be recalculated every time it is accessed), a materialized view contains a physical copy of the data. Therefore, you can query data directly from a materialized view without executing complex SQL statements, significantly improving query performance—this is known as trading space for time to accelerate queries.
Features
Types of materialized views
Non-real-time materialized view
As the name suggests, a non-real-time materialized view does not store the most up-to-date result set. It is not updated immediately with each modification to the base table. Instead, it is refreshed at predetermined intervals or manually triggered. When querying a non-real-time materialized view, only the data actually stored physically is retrieved. This approach is suitable for scenarios where data freshness is not a critical requirement but query performance is highly desired.
Real-time materialized view
A real-time materialized view is a database object that supports fetching real-time data. It uses the mechanism of materialized view logs (mlogs) to capture and process changes to the underlying base tables, ensuring the data in the materialized view reflects the latest state promptly. During a query, a real-time materialized view integrates these changes on the fly through online computation, presenting users with 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.
Materialized view refresh strategies
OceanBase AI Database supports four refresh strategies for materialized views: complete refresh, incremental refresh (fast refresh), hybrid refresh, and never refresh. The details are as follows:
Complete refresh
Complete refresh is a more straightforward method. Each time a refresh operation is performed, the system re-executes the query statement corresponding to the materialized view, completely recalculates and overwrites the original view result data. This method is suitable for scenarios with relatively small data volumes.
Incremental refresh
Incremental refresh only processes the portion of data that has changed since the last refresh. To achieve precise incremental refresh, OceanBase AI Database implements a materialized view log function similar to Oracle MLOG (Materialized View Log), which tracks and records incremental update data of the base table in detail through logs, ensuring that the materialized view can be refreshed quickly and incrementally. The incremental refresh method is particularly suitable for business scenarios with large data volumes and frequent changes.
Hybrid refresh
First, an incremental refresh is attempted. If the incremental refresh fails, a complete refresh is executed.
Never refresh
A materialized view is refreshed only when it is created and is not allowed to be refreshed again after creation.
Materialized view refresh methods
OceanBase AI Database supports automatic and manual refresh methods for materialized views.
Automatic refresh
When creating a materialized view, you can configure the timing of refreshes and set an automatic refresh interval. You can use the
START WITH datetime_exprandNEXT datetime_exprclauses to specify the background automatic refresh schedule for the materialized view.Manual refresh
If a materialized view is not configured for automatic refresh or the automatic refresh interval is set to a long value, you can use the
DBMS_MVIEW.REFRESHpackage to perform a manual refresh, keeping the materialized view's data synchronized with the base table data.
Accelerate queries with materialized views
Materialized views can contain aggregations, joins, and subqueries, and can be indexed and partitioned to further improve performance.
Query rewrite
When creating a materialized view, you can specify
ENABLE QUERY REWRITEto enable automatic query rewrite. In this case, the system can rewrite queries against the original table into queries against the materialized view, reducing the amount of business logic modification required.Use indexes
You can decide to create indexes on certain fields based on your business requirements to speed up queries on those fields.
Select data storage format
OceanBase AI Database supports both row-based and columnar storage formats for materialized views, providing better query performance in complex analytical scenarios that involve materialized view references.
Use primary keys
Users can specify a primary key for a materialized view to optimize performance for single-row searches, range queries, or join scenarios based on the primary key.
Use 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.
Application scenarios
Materialized views are used to optimize query performance, especially when processing large amounts of data and complex queries. By precomputing and storing the query results of a view, they reduce real-time computation to improve query performance and simplify complex query logic. They are commonly used in rapid report generation and data analysis scenarios.
- Frequent queries on the same data: Materialized views are suitable for storing complex queries that are frequently recalculated and resource-intensive, avoiding repeated computation with each query and improving query efficiency.
- Data pre-aggregation: Pre-summarize daily, weekly, or monthly sales data, or aggregate user behavior statistics. For example, scenarios like report generation and data analysis can leverage materialized views to precompute and store summarized data, reducing the time required for real-time computation.
- Large-scale data analysis: For businesses with large volumes of data, queries are typically time-consuming. Using materialized views avoids extensive scanning of the original data.
- Real-time analysis: For businesses with high real-time requirements, real-time materialized views can be used to accelerate queries and meet real-time business needs.
- Multidimensional data analysis: Use materialized views to precompute aggregated data for various dimension combinations, providing fast multidimensional query responses.
Considerations
- Storage overhead: Materialized views occupy additional storage space; consider disk capacity.
- Refresh (maintenance) cost: Automatic or manual refresh of materialized views consumes system resources. If the data in the base table 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 when the original data is updated. If the data in the base table changes, the data in the view 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 table of a materialized view
In a database, the base table of a materialized view refers to the original table or view referenced when creating that materialized view.
Type |
Can be used as base table |
|---|---|
| Regular table | Yes |
| Materialized view | Yes |
| Regular view | Yes.
NoteWhen a regular view is declared as a dimension table in OceanBase AI Database ( |
| Synonym | No |
| External table | Yes.
NoteOceanBase AI Database supports using external tables as base tables for materialized views to create fully refreshed materialized views. |
Limitations
You cannot perform INSERT, DELETE, or ALTER operations on a materialized view.
Performing DDL operations on the base table of a materialized view may prevent the materialized view from refreshing in the expected manner.
- For a complete refresh, the materialized view can be refreshed 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 automatic materialized view log management) to enable incremental refresh.
Materialized views do not support the XML data type.
Materialized views do not support table-level recovery.
When you drop a materialized view, it is not moved to the recycle bin. However, when you execute the
drop databasestatement, all materialized views in the database are moved to the recycle bin together with the database.After you specify a primary key for a materialized view, if the data does not satisfy the primary key constraint during maintenance or update, the view maintenance will fail. For example, when you execute the statement
CREATE MATERIALIZED VIEW mv1(PRIMARY KEY(c1)) AS SELECT c1 FROM t1;to create the materialized viewmv1, if thec1column in thet1table contains null values, an error will be returned during the data creation or maintenance of the materialized view.
Privileges
- You must have the
CREATE TABLEprivilege to create a materialized view. - You must have the
DROP TABLEprivilege to drop a materialized view. - You must have the
SELECTprivilege on all base tables for a complete refresh. - You must have the
SELECTprivilege on all base tables and the corresponding materialized view log for an incremental refresh. - Only the
SELECTprivilege can be granted on a materialized view. Other DML operations are not supported.