Accelerate queries by using materialized views|V4.3.5| docs|Distributed Database

Accelerate queries by using materialized views

Last Updated：2025-11-18 14:33:32 Updated

A materialized view (MV) is a database object that stores the results of a query. Unlike a standard view, a materialized view saves the output of the query, allowing you to reuse the results directly when querying. By storing the results of time-consuming operations (such as aggregation and joins), materialized views help avoid repeated execution of resource-intensive tasks and thus accelerate queries. This is especially beneficial in data warehouses and decision support systems, where materialized views can significantly reduce computation time and improve query efficiency.

Characteristics of materialized views

Refresh methods for materialized views

When you create a materialized view, you can choose different refresh methods to keep its data synchronized with the base table. The refresh method you select directly affects system performance and the real-time accuracy of query results.

Complete refresh and incremental refresh

Complete refresh: With a complete refresh, the materialized view re-executes the query each time it is refreshed, overwriting the previous results with new data. This method is suitable for scenarios with low latency requirements, infrequent updates to the base table, or complex queries involving small amounts of data.
Incremental refresh: Also called fast refresh, this method only updates the materialized view with incremental changes, making it especially suitable for large datasets. Incremental refresh requires a materialized view log (mlog) and has specific requirements for the query statement. For more information, see Refresh a materialized view in MySQL-compatible mode and Refresh a materialized view in Oracle-compatible mode. This method is ideal for scenarios with high latency requirements, large data volumes, and frequent changes.

Automatic refresh and manual refresh

Automatic refresh: When creating a materialized view, you can specify a refresh interval. The system will automatically schedule refresh tasks based on the configured timing rules.
Manual refresh: If automatic refresh is not set, or the refresh interval is long, you can manually execute a refresh command to keep the materialized view’s data synchronized with the base table.

Real-time materialized views

A real-time materialized view is a materialized view that can be queried to obtain real-time data. The query result is identical to that obtained by directly querying the base table, while leveraging the precomputed results in the materialized view to accelerate queries. Real-time materialized views capture and process changes in the base table through a materialized view log, ensuring that the data is always up to date. Because real-time materialized views rely on materialized view logs, their query statements must meet the same requirements as those for materialized views that support incremental refresh. Therefore, a real-time materialized view can be defined only if the query statement satisfies the requirements for incremental refresh.

For more information about creating real-time materialized views, see Create a materialized view in MySQL-compatible mode and Create a materialized view in Oracle-compatible mode.

Nested materialized views

A nested materialized view is a materialized view referenced by another materialized view. This approach is especially useful in ETL (Extract, Transform, Load) processes, where nested materialized views can aggregate and store results from different stages as independent views, avoiding repeated calculations and improving overall ETL efficiency. However, automatic cascading refresh is not supported for nested materialized views. Before using nested materialized views, it is important to understand the refresh considerations to ensure that querying the upper-level materialized view returns the expected data.

For more information about creating nested materialized views, see Create a materialized view in MySQL-compatible mode and Create a materialized view in Oracle-compatible mode.

Materialized view query rewriting

If you want to accelerate queries with materialized views without modifying the original query statements, you can take advantage of materialized view query rewriting. The system automatically matches query statements with materialized view definitions. If a matching materialized view is found, the system rewrites the query to use the materialized view, significantly improving query performance and efficiency without requiring changes to business logic.

For more information about materialized view query rewriting, see Materialized view query rewriting in MySQL-compatible mode and Materialized view query rewriting in Oracle-compatible mode.

Query acceleration using materialized views

Materialized views are a key mechanism for improving query performance in databases. By precomputing and storing query results, they reduce the overhead of real-time computations. To further optimize the query performance of materialized views, consider the following approaches:

Storage formats for materialized view data

Depending on the application scenario, you can choose row-based or columnar materialized views to accelerate queries.

Row-based materialized views: Suitable when the data has already been aggregated and queries mostly access entire data rows.
Columnar materialized views: Ideal for wide tables with large volumes of data and many columns, where queries focus on data analysis and aggregation.

Primary key materialized views

Primary keys ensure data uniqueness in materialized views and allow for more efficient search and update operations.

Indexing materialized views

Creating indexes on materialized views can significantly enhance query performance. Indexes enable quick location of required data, reducing the need for full-table scans.

Materialized views are a powerful tool for query acceleration, but their performance may be affected when handling large-scale data. Further query acceleration can be achieved by optimizing how queries are performed on materialized views.

Examples of creating materialized views

Notice

All the following examples are performed in MySQL-compatible mode.

Create source tables

First, we need to create source tables to store the original data. In this example, we create a sales table and an items table, which contain the sales data and item information, respectively.

Create the sales table.

CREATE TABLE sales (
order_id   INT PRIMARY KEY,
user_id    INT,
item_id    INT,
item_count INT,
region     VARCHAR(100)
);

Create the items table.

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 materialized view with complete refresh

We will create a materialized view based on the sales table. This materialized view will aggregate sales by product and region to accelerate queries.

Create a materialized view named mv_sales_summary that aggregates sales 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 of creating a materialized view, the following options are specified:

PRIMARY KEY: Specifies the primary key for the materialized view.
REFRESH COMPLETE: Specifies to use complete refreshes.
START WITH sysdate() NEXT sysdate() + interval 1 hour: Specifies to perform scheduled refreshes on an hourly basis.

Create a materialized view with incremental refresh

In scenarios where data changes frequently, incremental refreshes can significantly improve refresh efficiency. Before creating a materialized view with incremental refresh, you need to create a materialized view log on the base table of the materialized view. This is necessary to successfully create the materialized view with incremental refresh.

Note

For OceanBase Database V4.3.5, the automatic management of materialized view logs is supported starting from BP4 of this version. If automatic management of materialized view logs is enabled, you do not need to manually create a materialized view log on the base table before creating a materialized view with incremental refresh. OceanBase Database will automatically create the corresponding materialized view log on or update the existing materialized view log table definition to include the columns required by the new materialized view. For more information, see Automatic management of materialized view logs (MySQL-compatible mode) and Automatic management of materialized view logs (Oracle-compatible mode).

For more information about materialized view logs, see Materialized view logs in MySQL-compatible mode and Materialized view logs in Oracle-compatible mode.

The following examples demonstrate three query scenarios supported by materialized views with incremental refresh:

Single-table aggregation

Create a materialized view log on the sales table.

CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY (item_id, item_count, region) INCLUDING NEW VALUES;

Create a materialized view with incremental refresh for single-table aggregation named mv_sales_summary_fast.

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, count(*)  as c, count(item_count) as count
        FROM sales
        GROUP BY item_id, region;

Multi-table join

Create a materialized view log on the sales table.
1. (Optional) Drop the materialized view log on the sales table.
  
  If you have not created a materialized view log on the sales table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON sales;
```
2. Create a materialized view log on the sales table.
```
CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY (user_id, item_id, item_count, region) INCLUDING NEW VALUES;
```

Create a materialized view log on the items table.

CREATE MATERIALIZED VIEW LOG ON items
    WITH PRIMARY KEY (price_per_item, pic_url) INCLUDING NEW VALUES;

Create a materialized view with incremental refresh for multi-table join named mv_sales_items_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.order_id AS i_id,
            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 a materialized view log on the sales table.
1. (Optional) Drop the materialized view log on the sales table.
  
  If you have not created a materialized view log on the sales table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON sales;
```
2. Create a materialized view log on the sales table.
```
CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY (item_id, item_count, region) INCLUDING NEW VALUES;
```
Create a materialized view log on the items table.
1. (Optional) Drop the materialized view log on the items table.
  
  If you have not created a materialized view log on the items table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON items;
```
2. Create a materialized view log on the items table.
```
CREATE MATERIALIZED VIEW LOG ON items
    WITH PRIMARY KEY (price_per_item) INCLUDING NEW VALUES;
```

Create a materialized view with incremental refresh for multi-table join aggregation named mv_sales_item_join_group.

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,
            count(*) AS c,
            count(s.item_count * i.price_per_item) AS count
        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 the base table when the data changes. To create a real-time materialized view, you must first create a materialized view log.

Note

For OceanBase Database V4.3.5, the automatic management of materialized view logs is supported from V4.3.5 BP4. If automatic management of materialized view logs is enabled, you do not need to create materialized view logs for the base table before creating the real-time materialized view. OceanBase Database automatically creates the corresponding materialized view logs or updates the existing materialized view log table definitions to include the columns required for the new materialized view. For more information, see Automatic management of materialized view logs (MySQL-compatible mode) and Automatic management of materialized view logs (Oracle-compatible mode).

Create a materialized view log on the sales table.
1. (Optional) Drop the materialized view log on the sales table.
  
  If you have not created a materialized view log on the sales table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON sales;
```
2. Create a materialized view log on the sales table.
```
CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY (item_id, item_count, region) INCLUDING NEW VALUES;
```

Create the 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,
            count(*) as c,
            count(item_count) as count
        FROM sales
        GROUP BY item_id, region;

In this example, ENABLE ON QUERY COMPUTATION is enabled, which ensures that the materialized view is updated in real time during queries to obtain the latest data. This also meets the requirements for creating an incremental refresh materialized view.

Create a nested materialized view

In an ETL process, a nested materialized view combines multiple materialized views into a more complex data processing flow. In this example, two materialized views are created: one to associate sales information with item information, and another based on the first materialized view with additional aggregation.

Create a materialized view log on the sales table.
1. (Optional) Drop the materialized view log on the sales table.
  
  If you have not created a materialized view log on the sales table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON sales;
```
2. Create a materialized view log on the sales table.
```
CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY (user_id, item_id, item_count, region) INCLUDING NEW VALUES;
```
Create a materialized view log on the items table.
1. (Optional) Drop the materialized view log on the items table.
  
  If you have not created a materialized view log on the items table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON items;
```
2. Create a materialized view log on the items table.
```
CREATE MATERIALIZED VIEW LOG ON items
    WITH PRIMARY KEY (price_per_item,pic_url) INCLUDING NEW VALUES;
```

Create a materialized view named mv1_sales_items_join to associate sales information with item information.

CREATE MATERIALIZED VIEW mv1_sales_items_join
    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;

Create a materialized view log on the materialized view mv1_sales_items_join.

CREATE MATERIALIZED VIEW LOG ON mv1_sales_items_join
    WITH PRIMARY KEY (region,quantity,price_per_item) INCLUDING NEW VALUES;

Create a materialized view named mv2_join_sum based on the materialized view mv1_sales_items_join.

CREATE MATERIALIZED VIEW mv2_join_sum
    REFRESH FAST
    START WITH sysdate()
        NEXT sysdate() + interval 1 hour
    AS SELECT
            region,
            sum(quantity * price_per_item) AS sum_price,
            count(*) as c,
            count(quantity * price_per_item) as count
        FROM mv1_sales_items_join
        GROUP BY region;

Create a columnar materialized view

If you want to improve query efficiency in scenarios involving large amounts of data, you can create a columnar materialized view. In a columnar storage format, only the required columns are read during a query, significantly reducing disk I/O.

Create a materialized view log on the sales table.
1. (Optional) Drop the materialized view log on the sales table.
  
  If you have not created a materialized view log on the sales table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON sales;
```
2. Create a materialized view log on the sales table.
```
CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY (user_id, item_id, item_count, region) INCLUDING NEW VALUES;
```
Create a materialized view log on the items table.
1. (Optional) Drop the materialized view log on the items table.
  
  If you have not created a materialized view log on the items table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON items;
```
2. Create a materialized view log on the items table.
```
CREATE MATERIALIZED VIEW LOG ON items
    WITH PRIMARY KEY (price_per_item,pic_url) INCLUDING NEW VALUES;
```

Create a columnar materialized view named 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, we specified WITH COLUMN GROUP(each column), so that the materialized view uses the columnar storage format. This is particularly useful for OLAP scenarios, especially when dealing with large amounts of data and wide table queries.

Create an indexed materialized view for query rewriting

In this example, the ENABLE QUERY REWRITE clause is specified to create a full-refresh materialized view that can perform query rewriting.

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 specified to create a real-time materialized view that can perform query rewriting.

Create a materialized view log on the sales table.
1. (Optional) Drop the materialized view log on the sales table.
  
  If you have not created a materialized view log on the sales table, skip this step.
```
DROP MATERIALIZED VIEW LOG ON sales;
```
2. Create a materialized view log on the sales table.
```
CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY (item_id, item_count, region) INCLUDING NEW VALUES;
```

Create the real-time materialized view mv_sales_summary_com_select that can perform query rewriting.

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,
            count(*)  as c,
            count(item_count) as count
        FROM sales
        GROUP BY item_id, region;

Create materialized view indexes

To further optimize query performance, you can create indexes on materialized views. Materialized view indexes help speed up queries on materialized views, especially when dealing with large amounts of data.

(Optional) Create a materialized view named mv_sales_summary that summarizes sales by product and region.

If you have already created the mv_sales_summary materialized view based on the example in Create a materialized view with full refresh, 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 named idx_mv_sales_summary on the region column of the mv_sales_summary materialized view.
```
CREATE INDEX idx_mv_sales_summary ON mv_sales_summary (region);
```
This index can accelerate queries on the mv_sales_summary materialized view, especially those based on the region column.

References

For more information about materialized views, see Overview of materialized views (MySQL-compatible mode) and Overview of materialized views (Oracle-compatible mode).
For more information about refreshing materialized views, see Refresh materialized views (MySQL-compatible mode) and Refresh materialized views (Oracle-compatible mode).
For more information about materialized view logs, see Materialized view logs (MySQL-compatible mode) and Materialized view logs (Oracle-compatible mode).
For more information about deleting materialized views, see Delete materialized views (MySQL-compatible mode) and Delete materialized views (Oracle-compatible mode).