A materialized view (MV) is a database object that stores the query results of a view. Unlike a regular view, an MV physically stores the results of time-consuming operations such as aggregations and joins, allowing queries to reuse these results instead of recomputing them. This significantly reduces computation time and improves query efficiency, especially in data warehouses and decision support systems.
Why use materialized views in AP/real-time data warehouses
- Reduce duplicate computation: Multidimensional analysis, wide table joins, and periodic reports often involve repeated JOINs and GROUP BY operations. After materialization, these are maintained uniformly by refresh tasks.
- Control freshness and cost: By using strategies such as complete, incremental, and real-time (on demand), you can balance latency and resource usage (for capability selection, see Best practices for classic materialized view scenarios).
- Combine with other AP capabilities: For example, combine materialized results in columnar storage, materialized results hit by query rewrite, and external tables for lakehouse synergy (for scenarios and step-by-step SQL, see Best practices for classic materialized view scenarios).
Application scenarios
- Data aggregation: Aggregate transaction details, behavioral details, and more by day, week, or month.
- Fixed reports/metrics layer: Precompute metric tables for report or API queries.
- Optimize heavy queries: Materialize resource-intensive SQL results to avoid repeated execution.
- Data proximity and replica strategy (based on business planning): Within compliance constraints, redundantly aggregate results and use them together with routing and replica strategies.
- Monitoring pre-aggregation: Pre-aggregate monitoring details by time granularity.
Core capabilities
Query acceleration
Single-table aggregation: For scenarios where a business table contains a large amount of existing data and group aggregation is frequently required, you can create a materialized view with incremental refresh (refresh fast) for single-table group aggregation. If you need to merge the latest changes at query time, you can use a real-time materialized view (ENABLE ON QUERY COMPUTATION) when the conditions are met.
Multi-table join: For scenarios where multiple business tables contain a large amount of existing data and join queries are frequently required, you can create a materialized view with incremental refresh (refresh fast) for multi-table joins. After the data is properly maintained, a multi-table JOIN materialized view can be used together with query rewrite to automatically redirect queries on the base tables to the materialized results.
Materialized views and real-time data warehouses
In the AP/real-time data warehouse scenario of OceanBase AI Database, materialized views are commonly used to transform repeated computations on detail or wide tables into refreshable physical result sets. These result sets are organized by time (batch or near real-time), space (dimensions and granularity), and topic (business domain) to support multidimensional aggregation and derived metrics. When combined with columnar storage, query rewrite, and incremental refresh, they reduce the scanning and computational pressure on base tables for online analytical processing.
The role of materialized views in real-time data warehouses
A typical real-time or quasi-real-time data warehouse pipeline can be summarized as: Access (CDC/file/external table) → Detail layer (DWD) → Aggregation and topic layer (DWS/ADS) → Query and reporting. Materialized views are suitable for handling the repeatable aggregation, join, and wide table flattening steps:
- Multidimensional aggregation: Perform
GROUP BYon fact tables and join dimensions by region, category, time window, etc. - Data layering: Balance latency, resource usage, and result consistency by combining complete/incremental/real-time materialized views with scheduled or manual refresh.
- Query acceleration: While retaining SQL access to detail tables, automatically direct queries through materialized results via query rewrite, reducing modifications to business SQL.
For more information, see Overview of materialized views.
Refresh capability: Align materialized results with base tables
Materialized views need to be aligned with base table data through a refresh strategy.
Key capability |
Description |
Practice entry |
|---|---|---|
| Complete refresh | The result is recalculated and overwritten each time according to the definition. This method is suitable for scenarios where the definition is complex, a longer window is acceptable, or complete alignment is required. | Refresh a materialized view |
| Incremental refresh (fast refresh) | Based on Mlog, it is suitable for scenarios with large base tables and relatively localized changes; however, it imposes restrictions on SQL syntax. | Same as above; for Mlog, see Materialized view logs |
| Automatic/manual refresh | You can configure scheduling during creation, or manually refresh the materialized view as needed (for example, DBMS_MVIEW.REFRESH). |
Create a materialized view |
| Automatic Mlog management | Reduce the cost of manual Mlog maintenance on supported versions. | Materialized view logs |
| Refresh resource isolation | Large-scale refreshes and Mlog maintenance can be isolated from online workloads. | Materialized view resource isolation |
You can also tune parallelism by using system variables such as mview_refresh_dop.
Query rewrite capability: Hit materialized results with minimal business SQL modification
Query rewrite refers to the optimizer matching a query on base tables (or a semantically equivalent query) against an existing materialized view and using the precomputed results to answer the query.
- You can enable
ENABLE QUERY REWRITEwhen creating a materialized view (for specific syntax and limitations, see the documentation). - Whether to rewrite is affected by session/global variables, such as query_rewrite_enabled and query_rewrite_integrity.
For detailed rules and examples, see Query rewrite using materialized views.
Materialized view capabilities and selection
To avoid repeating the earlier sections on real-time data warehouse positioning, refresh capability, and query rewrite, this section only keeps the concise decision-making points. Examples and SQL statements are provided in the following sections.
Refresh strategies (complete, fast, automatic, and manual)
- Complete refresh: During a complete refresh, the materialized view re-executes the query statement and overwrites the original view result data with the newly computed results. This is suitable for scenarios with low latency requirements, infrequent base table data updates, complex query statements, or small data volumes.
- Fast refresh: Also known as incremental refresh, this strategy relies on a materialized view log (Materialized View Log, Mlog) and has certain requirements for query statements. Currently, it supports query statements for single-table aggregation, multi-table joins, and multi-table join aggregation, with specific requirements for aggregate functions and join methods, making it suitable for scenarios with large data volumes and frequent changes.
- Automatic refresh: When creating a materialized view, you can specify its refresh interval. The system then automatically schedules refresh tasks based on the configured refresh time rules.
- Manual refresh: If a materialized view is not configured for automatic refresh or the automatic refresh interval is too long, you can manually execute the refresh command to keep the materialized view data synchronized with the base table data.
For detailed syntax and limitations, see Refresh a materialized view.
Real-time materialized view (ON QUERY COMPUTATION)
Real-time materialized views allow you to obtain real-time data by querying the materialized view. The query results are the same as those obtained by directly querying the base table, and the precomputed results of the materialized view can be used to accelerate queries. Real-time materialized views capture and process changes to the underlying base tables by using the Mlog mechanism, ensuring that the data in the materialized view reflects the latest state in a timely manner. Because they rely on Mlogs, their query statements must meet the same requirements as those for fast refresh. In other words, only materialized views that support fast refresh can be defined as real-time materialized views.
For more information about creating real-time materialized views, see Create a materialized view.
Query rewrite (ENABLE QUERY REWRITE)
When you want to use a materialized view to accelerate queries but do not want to modify the original query statement, you can utilize the query rewrite capability of the materialized view. The system can automatically match the query statement with the materialized view definition. If a matching materialized view is found, it automatically rewrites the query into one that uses the materialized view. This can significantly improve query performance and efficiency without requiring any changes to your business logic.
For more information about query rewrite for materialized views, see Query rewrite using materialized views.
Query acceleration optimization methods
Optimization dimension |
Best practice |
Application scenarios |
Benefit |
|---|---|---|---|
| Choose the materialized result storage based on the query pattern | Wide-table analysis favors columnar storage, while row-level access favors row-based storage. | Scenarios where a balance between scan efficiency and row access efficiency is required. | Reduce unnecessary column reads, lower I/O, and improve query efficiency. |
| Storage format of materialized view data | Choose row-based storage or columnar storage based on your actual application scenarios. Row-based storage is suitable for scenarios where "results are aggregated and full-row access is predominant"; columnar storage is suitable for scenarios involving "wide tables and aggregate analysis". | Report aggregation, wide-table analysis, and fixed-metric queries. | It better fits the access pattern and reduces scan overhead. |
| Use primary key materialized views | Define a primary key for the materialized view to ensure the uniqueness of the result set. | Materialized results that require stable positioning, updating, or deduplication. | Improve search and update efficiency while reducing the risk of duplicate data. |
| Create indexes based on materialized views | Create indexes on filter columns, join columns, and sort columns. | High-frequency filtering, association, and sorting queries. | Reduce full table scans and accelerate hit paths. |
| Refresh strategy based on SLA | Balance latency, freshness, and resources, and choose between complete/incremental and automatic/manual refresh as needed. | AP and reporting scenarios with different real-time requirements. | Avoid pursuing real-time performance blindly; instead, focus on controlling resource costs while maintaining acceptable performance. |
Materialized views are an important means of query acceleration. For large-scale data scenarios, you can continuously optimize query performance through the synergy of "storage format + index + refresh strategy".
Create a materialized view
Create source tables
First, we need to create source tables to store raw data. In this example, we create a sales table and an items table containing sales data and product information, respectively.
Create the sales data table
sales.CREATE TABLE sales ( order_id INT PRIMARY KEY, user_id INT, item_id INT, item_count INT, region VARCHAR(100) );Create the product information table
items.CREATE TABLE items ( order_id INT, product_id INT, quantity INT, price_per_item DECIMAL(10, 2) NOT NULL, pic_url VARCHAR(1000), PRIMARY KEY (order_id, product_id) );
Create a complete refresh materialized view
This example creates a materialized view based on the sales table to accelerate queries.
Create a materialized view mv_sales_summary that aggregates sales volume by product and region.
CREATE MATERIALIZED VIEW mv_sales_summary(PRIMARY KEY(item_id))
REFRESH COMPLETE
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS SELECT item_id, region, SUM(item_count) AS total_count
FROM sales
GROUP BY item_id, region;
In this example, we specify the following characteristics for the materialized view:
PRIMARY KEY: Specifies the primary key for the materialized view.REFRESH COMPLETE: Indicates that complete refresh is used.START WITH sysdate() NEXT sysdate() + interval 1 hour: Specifies hourly scheduled refresh.
Create an incremental refresh materialized view
For scenarios with frequent data changes, incremental refresh can be used to improve refresh efficiency. Before you create a materialized view with incremental refresh, you must create a materialized view log (Mlog) on its base table. OceanBase AI Database enables automatic management of materialized view logs by default. When you create an incremental refresh materialized view or a real-time materialized view, OceanBase AI Database automatically creates the Mlog or updates its definition.
For more information about materialized view logs, see Materialized view logs.
The following examples demonstrate three query scenarios supported by incremental refresh materialized views:
Single-table aggregation
Create an incremental refresh materialized view mv_sales_summary_fast for single-table aggregation.
CREATE MATERIALIZED VIEW mv_sales_summary_fast
REFRESH FAST
START WITH sysdate() NEXT sysdate() + interval 1 hour
AS SELECT
item_id,
region,
SUM(item_count) AS total_count
FROM sales
GROUP BY item_id, region;
Multi-table join
Create an incremental refresh materialized view mv_sales_items_join for multi-table join.
CREATE MATERIALIZED VIEW mv_sales_items_join
PARTITION BY HASH(order_id)
PARTITIONS 10
REFRESH FAST
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS SELECT
s.order_id AS order_id,
s.user_id AS customer_id,
s.item_id AS item_id,
s.item_count AS quantity,
s.region AS region,
i.product_id AS i_item_id,
i.price_per_item AS price_per_item,
i.pic_url AS pic_url
FROM sales s JOIN items i
ON s.order_id = i.order_id;
Multi-table join aggregation
Create an incremental refresh materialized view mv_sales_item_join_group for multi-table join aggregation.
CREATE MATERIALIZED VIEW mv_sales_item_join_group
REFRESH FAST
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS SELECT
s.item_id AS item_id,
s.region AS region,
SUM(s.item_count * i.price_per_item) AS sum_price
FROM sales s JOIN items i
ON s.order_id = i.order_id
GROUP BY item_id, region;
Create a real-time materialized view
A real-time materialized view ensures that the query results of the materialized view are synchronized with those of the base tables whenever data changes. Like incremental refresh materialized views, real-time materialized views require the creation of materialized view logs before they can be created. OceanBase AI Database enables automatic materialized view log management by default. When you create an incremental refresh materialized view or a real-time materialized view, OceanBase AI Database automatically creates the Mlog or updates its definition.
Create a real-time materialized view mv_sales_summary_com.
CREATE MATERIALIZED VIEW mv_sales_summary_com
REFRESH FORCE
START WITH sysdate()
NEXT sysdate() + interval 1 hour
ENABLE ON QUERY COMPUTATION
AS SELECT
item_id,
region,
SUM(item_count) AS total_count
FROM sales
GROUP BY item_id, region;
In this example, we enable ENABLE ON QUERY COMPUTATION so that the materialized view is updated in real time during queries, ensuring that the latest data is obtained. The query statement for the materialized view meets the requirements for creating an incremental refresh materialized view.
Create a columnar materialized view
If you want to improve query efficiency in big data analysis scenarios, you can create a columnar materialized view. By storing data in columns, only the required columns are read during queries, significantly reducing disk I/O.
Create a columnar materialized view wide_sales_column.
CREATE MATERIALIZED VIEW wide_sales_column
WITH COLUMN GROUP(each column)
REFRESH FAST
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS SELECT
s.order_id AS order_id,
s.user_id AS customer_id,
s.item_id AS item_id,
s.item_count AS quantity,
s.region AS region,
i.order_id AS i_id,
i.product_id AS i_item_id,
i.price_per_item AS price_per_item,
i.pic_url
FROM sales s JOIN items i
ON s.order_id = i.order_id;
In this example of creating a columnar materialized view, we specify WITH COLUMN GROUP(each column) so that the materialized view uses the columnar storage format. This is particularly useful in OLAP scenarios, especially for queries involving large amounts of data and wide tables.
Create a materialized view for query rewrite
In this example, the ENABLE QUERY REWRITE clause is used to create a complete refresh materialized view that supports query rewrite.
CREATE MATERIALIZED VIEW mv_sales_summary_select
REFRESH COMPLETE
START WITH sysdate()
NEXT sysdate() + interval 1 hour
ENABLE QUERY REWRITE
AS SELECT item_id, region, SUM(item_count) AS total_count
FROM sales
GROUP BY item_id, region;
In this example, the ENABLE QUERY REWRITE and ENABLE ON QUERY COMPUTATION clauses are used to create a real-time materialized view that supports query rewrite.
Create a real-time materialized view mv_sales_summary_com_select that supports query rewrite.
CREATE MATERIALIZED VIEW mv_sales_summary_com_select
REFRESH FAST
START WITH sysdate() NEXT sysdate() + interval 1 hour
ENABLE ON QUERY COMPUTATION
ENABLE QUERY REWRITE
AS SELECT
item_id,
region,
SUM(item_count) AS total_sales
FROM sales
GROUP BY item_id, region;
Create a materialized view index
To further optimize query performance, you can create an index on a materialized view. A materialized view index helps accelerate queries against the materialized view, especially when a large amount of data is involved.
(Optional) Create a materialized view
mv_sales_summarythat summarizes sales volumes by product and region.If you have already created the materialized view
mv_sales_summaryfollowing the example in Create a complete refresh materialized view, skip this step.CREATE MATERIALIZED VIEW mv_sales_summary(PRIMARY KEY(item_id)) REFRESH COMPLETE START WITH sysdate() NEXT sysdate() + interval 1 hour AS SELECT item_id, region, SUM(item_count) AS total_count FROM sales GROUP BY item_id, region;Create an index
idx_mv_sales_summaryon theregioncolumn of the materialized viewmv_sales_summary.CREATE INDEX idx_mv_sales_summary ON mv_sales_summary (region);This index accelerates queries on the
mv_sales_summarymaterialized view, especially those based on theregioncolumn.
Dictionary and O&M views
The following table describes the system views commonly used for troubleshooting materialized views, Mlogs, and refresh tasks:
DBA view |
ALL view |
USER view |
Description |
|---|---|---|---|
DBA_MVIEW_LOGS |
ALL_MVIEW_LOGS |
USER_MVIEW_LOGS |
Describes information about materialized view logs. |
DBA_MVIEWS |
ALL_MVIEWS |
USER_MVIEWS |
Describes materialized view information. |
DBA_MVREF_STATS_SYS_DEFAULTS |
USER_MVREF_STATS_SYS_DEFAULTS |
Describes the system-level default values of the refresh statistics attributes of materialized views. | |
DBA_MVREF_STATS_PARAMS |
USER_MVREF_STATS_PARAMS |
Describes the refresh statistics parameters associated with each materialized view. | |
DBA_MVREF_RUN_STATS |
USER_MVREF_RUN_STATS |
Describes the run information of each refresh of a materialized view. Each run is identified by the REFRESH_ID column. |
|
DBA_MVREF_STATS |
USER_MVREF_STATS |
Describes the basic timing statistics for materialized view refreshes. | |
DBA_MVREF_CHANGE_STATS |
USER_MVREF_CHANGE_STATS |
Describes the information about loading changed data from the base tables associated with refresh operations of all materialized views. | |
DBA_MVREF_STMT_STATS |
USER_MVREF_STMT_STATS |
Describes information associated with refresh statements. |