Materialized views in OceanBase AI Database can precompute query results and store them as physical objects, enabling continuous data processing and real-time queries. Core capabilities include:
- Data precomputation: Precompute and store query results to reduce real-time computation overhead and accelerate subsequent queries and analysis.
- Incremental refresh and streaming processing: Supports complete refresh (rebuilding all data) and incremental refresh (processing only changed data). Incremental refresh continuously processes changed data in a streaming manner, avoiding full recomputation. It is a core capability for building real-time data processing pipelines.
- Real-time queries: When enabled with
ENABLE ON QUERY COMPUTATION, queries automatically incorporate incremental data to return real-time results, further ensuring query result timeliness on top of precomputational acceleration. - Nested and cascading processing: Supports multiple layers of materialized view nesting to build complex ETL pipelines.

Overview
The following table categorizes the features of materialized views in OceanBase AI Database by capability level to help users understand the technical implementation and application scenarios.
Product capabilities |
Feature |
|---|---|
| Basic capabilities | Supports defining primary keys, creating partitions, creating table groups, creating indexes, and performing a full refresh. |
| Advanced capabilities | Supports query rewrite, incremental refresh, materialized views in columnar format, base tables with views and external tables, and bypass import. Supports adjusting refresh parallelism and scheduling intervals, resource isolation, monitoring and diagnostics, and ODC visualization operations. |
| Advanced capabilities | Real-time materialized views, nested materialized views, support for dimension-table joins, support for DDL operations, automatic materialized view log management Incremental refresh extended to support Outer Join/Union All |
Data processing scenarios
Scenario 1: Preprocess aggregated data in a single table
Application scenario
This applies to scenarios where a business has a large volume of existing data in a single table, high direct query latency, and requires frequent group aggregation queries (such as COUNT, SUM, GROUP BY).
Procedure
Create an incremental refresh materialized view:
CREATE MATERIALIZED VIEW mv1 PRIMARY KEY (a) PARALLEL 5 REFRESH FAST START WITH SYSDATE() NEXT SYSDATE() + INTERVAL '5' SECOND ENABLE ON QUERY COMPUTATION AS SELECT a, COUNT(b), COUNT(*), SUM(b) FROM t1 GROUP BY a;REFRESH FAST: Enables incremental refresh. Since theREFRESH FASTmethod uses record information in the materialized view log to determine what needs incremental refresh, you must create the base table's materialized view log (mlog) before creating the materialized view for incremental refresh.ENABLE ON QUERY COMPUTATION: Automatically merges incremental data during real-time queries.
Effect verification
Non-real-time queries directly read data from the materialized view, while real-time queries automatically merge incremental data and return results. Both have significantly lower latency than querying the base table. As the base table's data volume increases, the acceleration effect of the materialized view becomes more pronounced, as shown in the figure below.

Scenario 2: Preprocess joined data across multiple tables
Application scenario
This applies to scenarios where business tables contain large amounts of existing data, direct query latency is high, and frequent multi-table join queries are required, such as joining two or three tables.
Procedure
Create an incremental refresh materialized view:
CREATE MATERIALIZED VIEW mv2 PRIMARY KEY (a, b) PARALLEL 5 PARTITION BY HASH(a) PARTITIONS 4 REFRESH FAST START WITH SYSDATE() NEXT SYSDATE() + INTERVAL 5 SECOND ENABLE ON QUERY COMPUTATION AS SELECT t1.a, t1.b, t1.c, t2.d, t2.e FROM t1 JOIN t2 ON t1.b = t2.d;REFRESH FAST: Enables incremental refresh. Since theREFRESH FASTmethod uses record information in the materialized view log to determine what needs incremental refresh, you must create the materialized view log (mlog) of the base table before creating the materialized view for incremental refresh.PARTITION BY HASH: Improves parallel execution efficiency through partitioning.PARALLEL: Specifies the degree of parallelism to accelerate data processing.
Query optimization
For the original query
SELECT * FROM t1 JOIN t2 ON ..., OceanBase AI Database automatically rewrites it as a query on the materialized viewmv2. As the data volume of the base table increases, the precomputation advantage of the materialized view becomes more significant, as shown in the following figure.
Scenario 3: Streaming incremental processing and real-time data warehousing
Core advantages
Materialized views define ETL processes within the database using SQL, eliminating the need for external scheduling tools. Incremental refresh processes changed data in a streaming manner to achieve near-real-time data updates. Data stream tasks are implemented through transactions, ensuring ACID compliance.


Procedure
Create a multi-table join materialized view to perform dimensionality expansion on the data (multi-table joining):
CREATE MATERIALIZED VIEW mv_join PARALLEL 5 REFRESH FAST START WITH SYSDATE() NEXT SYSDATE() + INTERVAL '5' SECOND AS SELECT t1.a, t1.b, t1.c, t2.d, t2.e, t3.f, t3.g FROM t1 JOIN t2 ON t1.b = t2.d JOIN t3 ON t1.b = t3.f;REFRESH FAST: Enables incremental refresh. Since theREFRESH FASTmethod uses record information in the materialized view log to determine what needs incremental refresh, you must create the materialized view log (mlog) of the base table before creating the materialized view for incremental refresh.
Create an aggregate analysis materialized view based on
mv_join:CREATE MATERIALIZED VIEW mv_agg PARALLEL 5 REFRESH FAST START WITH SYSDATE() NEXT SYSDATE() + INTERVAL '5' SECOND AS SELECT b, SUM(c) AS sum_c, SUM(e) AS sum_e, SUM(g) AS sum_g, COUNT(*) AS cnt FROM mv_join GROUP BY b;Build a multi-layer ETL process
By nesting multiple levels of materialized views (for example,
mv_aggbased onmv_join), you can gradually implement complex data processing. Each layer of the materialized view can have its own refresh cycle independently set.
Key configuration notes
Refresh strategy selection
Complete refresh (REFRESH COMPLETE) is suitable for scenarios with infrequent data changes. Each refresh requires rebuilding all data, which consumes relatively high resources.
Incremental refresh (REFRESH FAST) processes only changed data and offers better performance. Currently, it supports SQL statements for single-table aggregation, multi-table association, and multi-table association aggregation, as well as single-table predicate filtering, Left Join, and Union All.
Real-time query capability
After real-time query (ENABLE ON QUERY COMPUTATION) is enabled, the latest data and incremental data in materialized views are automatically merged during query execution to return real-time results. This is suitable for real-time analytical queries that require query optimization.
Parallelism and partition optimization
The PARALLEL parameter specifies the degree of parallelism to accelerate data processing, while the PARTITION BY clause improves query performance through partitioning.