Accelerate queries with materialized views

A materialized view (MV) is a database object that differs from a regular view in that it stores the query results of the view. By caching the results of time-consuming operations such as aggregations or joins for direct reuse in queries, it avoids re-executing these resource-intensive operations, thereby accelerating queries. This is particularly beneficial in data warehouses and decision support systems, where it can significantly reduce computation time and improve query efficiency.

Why use materialized views in AP/real-time data warehouses

• Reduce duplicate computation: Multidimensional analysis, wide table joins, and periodic reports often involve repetitive JOINs and GROUP BY operations. After materialization, these are maintained uniformly by refresh tasks.
• Control freshness and cost: By using strategies such as full/incremental/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, use the materialized results in columnar storage, leverage materialized results when query rewrite succeeds, or combine them with 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 and behavioral details on a daily, weekly, or monthly basis.
• Fixed reports/metrics layer: Precompute metric tables for report generation or API queries.
• Query optimization: Materialize resource-intensive SQL results to avoid repeated executions.
• Data proximity and replica strategy (based on business planning): Redundantly aggregate results in compliance with regulations, and use routing and replica strategies.
• Monitoring pre-aggregation: Pre-aggregate monitoring details by time granularity.

Core capabilities

Query acceleration

• Single-table aggregation: For business scenarios where a single table has a large amount of existing data and frequent group aggregation queries are required, you can create an incremental refresh (refresh fast) materialized view for single-table group aggregation. To incorporate the latest changes upon query, you can use real-time materialized view (ENABLE ON QUERY COMPUTATION) when conditions permit.

• Multi-table join: For business scenarios involving multiple tables with a large amount of existing data and frequent join queries, you can create an incremental refresh (refresh fast) materialized view for multi-table joins. Once well-maintained, multi-table JOIN materialized views, combined with query rewrite, can automatically direct base table queries to use the materialized results.

Materialized views and real-time data warehouses

In OceanBase's AP/real-time data warehouse scenarios, materialized views are often used to precipitate repeated computations on detail or wide tables into refreshable physical result sets, organizing multidimensional aggregation and derived metrics in terms of time (batch/quasi-real-time), space (dimensionality/granularity), and topic (business domain). When combined with columnar storage, query rewrite, and incremental refresh, they can reduce the scanning and computational pressure on base tables for online analysis.

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, joining, and flattening of wide tables steps:

• Multidimensional aggregation: Perform GROUP BY on fact tables and join dimensions by region, category, time window, etc.
• Data layering: Balance latency, resource usage, and result consistency by combining full/incremental/real-time materialized views with scheduled or manual refresh.
• Query acceleration: While retaining SQL access to detail tables, use query rewrite to automatically route queries through materialized results, minimizing business modifications.

For more information, see Materialized view overview (MySQL) and Materialized view overview (Oracle).

Refresh capability: Align materialized results with base tables

Materialized views need to be aligned with base table data through a refresh strategy.

The degree of parallelism can be tuned using 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 the base table (or its equivalent semantics) with an existing materialized view and responding to the query with the precomputed result.

• You can enable ENABLE QUERY REWRITE when creating a materialized view (for specific syntax and limitations, refer to the manual).
• Whether rewriting occurs is influenced by session/global variables, such as query_rewrite_enabled and query_rewrite_integrity.

For detailed rules and examples, see Query rewrite for materialized views (MySQL) and Query rewrite for materialized views (Oracle).

Materialized view capabilities and selection

To avoid repetition with the previous sections on "real-time data warehouse positioning, refresh capability, and query rewrite capability," this section only retains the concise key points at the decision-making level. Examples and SQL statements are provided in the following chapters.

Refresh strategy (Complete / Fast / Automatic / Manual)

• Complete: Each time a refresh is performed, the materialized view re-executes the query statement and overwrites the original view results with the computed results. This is suitable for scenarios with low latency requirements, infrequent base table data updates, complex query statements, or small data volumes.
• Fast: Also known as fast refresh, fast refresh relies on the Materialized View Log (Mlog). It has specific requirements for query statements. Currently, it supports query statements for single-table aggregation, multi-table joins, and multi-table join aggregation, with certain requirements for aggregate functions and join methods, making it suitable for large data volume and high-frequency change scenarios.
• Automatic: When creating a materialized view, you can specify its refresh interval. The system will automatically schedule the refresh task based on the configured refresh timing rules.
• Manual: If a materialized view is not configured for automatic refresh or if the automatic refresh interval is large, you can manually execute the materialized view's refresh command to keep the materialized view's data synchronized with the base table data.

For detailed syntax and limitations, see Refresh a materialized view (MySQL) and Refresh a materialized view (Oracle).

Real-Time Materialized View (ON QUERY COMPUTATION)

As the name suggests, a real-time materialized view allows you to obtain real-time data by querying it. The query result is the same as directly querying the base table, and it can leverage the pre-computed results of the materialized view to accelerate queries. It uses the Mlog mechanism to capture and process changes to the underlying base tables, ensuring that the data in the materialized view reflects the latest state in a timely manner. Since it depends on Mlog, the query statement requirements for a real-time materialized view are the same as those for a fast refresh materialized view. That is, only materialized views that meet the requirements for fast refresh can be defined as real-time materialized views.

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.

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 definition of the materialized view. If a matching materialized view is found, it automatically rewrites the query to use the materialized view. This can significantly improve query performance and efficiency without requiring any business logic changes.

For more information about materialized view query rewrite, see Query rewrite for materialized views in MySQL-compatible mode and Query rewrite for materialized views in Oracle-compatible mode.

Query acceleration optimization methods

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".

Example of creating 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.

1. 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)
   );
   

2. 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 fully refreshed materialized view

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

Create a materialized view mv_sales_summary that summarizes sales volumes 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, we specify the following characteristics:

• PRIMARY KEY: Specifies the primary key for the materialized view.
• REFRESH COMPLETE: Indicates that full refresh is used.
• START WITH sysdate() NEXT sysdate() + interval 1 hour: Specifies to refresh hourly.

Create an incremental refreshed materialized view

For scenarios with frequent data changes, incremental refresh can be used to improve refresh efficiency. Before creating an incremental refreshed materialized view, you must create a materialized view log (Mlog) based on the base table of the materialized view. OceanBase Database enables automatic management of materialized view logs by default. When creating an incremental refreshed materialized view or a real-time materialized view, OceanBase Database automatically creates the Mlog or updates its definition.

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 an incremental refreshed materialized view:

Single-table aggregation

Create an incremental refreshed 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 with multi-table joins.

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;

Aggregation with multi-table join

Create an incremental refresh materialized view mv_sales_item_join_group with 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 result of the materialized view is synchronized with the base table when data changes. Since a real-time materialized view relies on materialized view logs, similar to an incremental refresh materialized view, you must create the materialized view log before creating the materialized view. OceanBase 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 Database automatically creates or updates the MLOG definition.

Create a real-time materialized view named 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 to ensure the latest data is obtained. The query statement for this 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 scenarios involving large volumes of data, you can create a columnar materialized view. By storing data in columns, it reads only the required columns during queries, significantly reducing disk I/O.

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 of creating a columnar materialized view, we specify WITH COLUMN GROUP(each column) to use the columnar storage format. This is very useful for OLAP scenarios, especially for queries involving large volumes of data and wide tables.

Create a materialized view for query rewrite

In this example, we specify ENABLE QUERY REWRITE to create a fully refreshed 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, we specify ENABLE QUERY REWRITE and ENABLE ON QUERY COMPUTATION 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.

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

   If you have already created the materialized view mv_sales_summary according to the example in Create a fully refreshed 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;
   

2. Create an index idx_mv_sales_summary on the region column of the materialized view mv_sales_summary.

   CREATE INDEX idx_mv_sales_summary ON mv_sales_summary (region);
   

   This index accelerates queries on the mv_sales_summary materialized view, especially those based on the region column.

Dictionary and O&M views

When troubleshooting materialized views, Mlogs, and refresh tasks, the following system views are commonly used (taking MySQL-compatible mode as an example; for the complete list and column descriptions, refer to the documentation of the current version).

References

• For detailed information and usage instructions on materialized views, see Materialized view overview (MySQL-compatible mode) and Materialized view overview (Oracle-compatible mode).
• For detailed information and usage instructions on refreshing materialized views, see Refresh a materialized view (MySQL-compatible mode) and Refresh a materialized view (Oracle-compatible mode).
• For detailed information and usage instructions on materialized view logs, see Materialized view log (MySQL-compatible mode) and Materialized view log (Oracle-compatible mode).
• For detailed information and usage instructions on dropping materialized views, see Drop a materialized view (MySQL-compatible mode) and Drop a materialized view (Oracle-compatible mode).