Classic scenarios for materialized views

This topic provides step-by-step SQL practice in MySQL-compatible mode (from orders basics to tbl* snippets, and full MVs with external tables), which you can follow section by section. Use this topic together with the sales/items mainline examples in Query acceleration with materialized views: For a complete drill requiring end-to-end business table coverage, read that document first.

Build a real-time data warehouse with materialized views: Scenario selection

To facilitate quick decision-making in real-time data warehouse construction, you can select the appropriate scenario in the order of "data freshness requirement -> computation complexity -> data source type".

The recommended practice application order is as follows:

1. Define the metric and refresh strategy first: Determine whether it is "minute-level incremental" or "real-time computation upon query".
2. Then define the refresh mechanism: Prioritize evaluating whether FAST meets the conditions; if not, use COMPLETE.
3. Finally, perform query-side optimization: Add columnar storage, indexes, and QUERY REWRITE as needed to avoid introducing too many variables at once.

Getting started: Full refresh and incremental refresh on an order table

The following uses a common order table from a business database to demonstrate the basic usage of full maintenance by default, incremental maintenance with FAST, manual refresh, and scheduled refresh (consistent with Refresh materialized views).

Create tables, populate data, and create a full materialized view

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

INSERT INTO orders VALUES
  (1, 10001, 1, 20, 100, 'HZ'),
  (2, 10002, 1, 10, 150, 'BJ'),
  (3, 10001, 2, 50, 50, 'SH');

CREATE MATERIALIZED VIEW mv1
AS
SELECT region, SUM(item_count * item_price) AS sum_price
FROM orders
GROUP BY region;

SELECT * FROM mv1;

When creating a materialized view, a first-time materialization is performed first; if the refresh strategy is not explicitly declared, a manual full refresh is usually required to keep it aligned with the base table.

Manual full refresh (DBMS_MVIEW.REFRESH)

After writing to the base table continues, the materialized result does not change automatically and requires a refresh:

INSERT INTO orders VALUES
  (4, 10002, 2, 10, 100, 'SH'),
  (5, 10003, 1, 2, 20, 'HZ');

SELECT * FROM mv1;

The result is as follows:

+--------+-----------+
| region | sum_price |
+--------+-----------+
| HZ     |      2000 |
| BJ     |      1500 |
| SH     |      2500 |
+--------+-----------+
3 rows in set

You can see that the materialized view result did not change automatically; you need to execute the following SQL to trigger a refresh:

CALL DBMS_MVIEW.REFRESH('mv1', 'c', refresh_parallel => 2);

SELECT * FROM mv1;

The result is as follows:

+--------+-----------+
| region | sum_price |
+--------+-----------+
| HZ     |      2040 |
| BJ     |      1500 |
| SH     |      3500 |
+--------+-----------+
3 rows in set

'c' indicates a complete full refresh; parameters such as refresh_parallel are subject to DBMS_MVIEW.REFRESH.

You can query the refresh operation status (example):

SELECT MVIEWS, METHOD, START_TIME, END_TIME
FROM oceanbase.DBA_MVREF_RUN_STATS;

Change to incremental refresh after deletion

DROP MATERIALIZED VIEW mv1;

CREATE MATERIALIZED VIEW mv1(region, c, sum_price, cnt_price)
    REFRESH FAST
    ON DEMAND START WITH sysdate() NEXT sysdate() + INTERVAL 5 MINUTE
    AS SELECT
           region,
           SUM(item_count * item_price) AS sum_price
       FROM orders
       GROUP BY region;

After inserting a new order, perform a manual incremental refresh:

1. Insert data.

   INSERT INTO orders VALUES (6, 10001, 3, 30, 70, 'HZ');
   

2. Manually refresh.

   CALL DBMS_MVIEW.REFRESH('mv1', 'f');
   

3. View the data in the materialized view.

   SELECT region, sum_price FROM mv1;
   

   If START WITH ... NEXT ... was configured during creation, you can also view the background refresh task in DBA_SCHEDULER_JOBS (specific fields are subject to the current version dictionary description).

Order of object cleanup

When deleting a materialized view and its base table, note that you must delete the MV first, then the base table.

DROP MATERIALIZED VIEW mv1;

DROP TABLE orders;

Scenario 1: Operational analytics and reporting queries

In operational analytics and reporting scenarios, common issues include many columns in detail tables, large scanning ranges, and frequent changes in query conditions.

These scenarios are typically read-heavy and are suitable for reducing unnecessary column scans using columnar materialized views. When necessary, they can be further optimized with indexes and query rewriting.

This section uses a simple example to illustrate how to use columnar materialized views to support wide-table queries and multidimensional analysis.

CREATE TABLE IF NOT EXISTS tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);

CREATE MATERIALIZED VIEW mv_ec_tbl1
    WITH COLUMN GROUP(each column)
    AS SELECT *
       FROM tbl1;

If query rewriting is used, it is generally required that the rewritten conditions can utilize indexes on the materialized view (rules see Query rewrite for materialized views):

CREATE INDEX idx1_mv_ec_tbl1 ON mv_ec_tbl1(col1);

Scenario 2: Real-time metric computation and queries

In real-time metrics scenarios, common requirements include hierarchical aggregation, incremental maintenance, low-latency queries, and minimal modification to business SQL.

These scenarios often require a combination of capabilities rather than a single one. Depending on the business's timeliness requirements, you can use nested materialized views, materialized view logs, FAST refresh, real-time materialized views, and query rewriting.

Nested materialized views

When a query involves multiple layers of aggregation or multiple steps of processing, you can first store the intermediate results in a materialized view and then create another materialized view based on those results. This approach is suitable for hierarchical precomputation scenarios, allowing complex metrics to be maintained in multiple steps.

CREATE TABLE IF NOT EXISTS tbl3(id INT, name VARCHAR(30), PRIMARY KEY(id));
CREATE TABLE IF NOT EXISTS tbl4(id INT, age INT, PRIMARY KEY(id));

CREATE MATERIALIZED VIEW mv1_tbl3_tbl4 (PRIMARY KEY (id1, id2))
    REFRESH COMPLETE
    AS SELECT tbl3.id id1, tbl4.id id2, tbl3.name, tbl4.age
        FROM tbl3, tbl4
        WHERE tbl3.id = tbl4.id;

CREATE MATERIALIZED VIEW mv_mv1_tbl3_tbl4
    REFRESH COMPLETE
    AS SELECT SUM(age) age_sum
        FROM mv1_tbl3_tbl4;

CREATE MATERIALIZED VIEW mv1_mv1_tbl3_tbl4
    REFRESH COMPLETE INCONSISTENT
    AS SELECT SUM(age) age_sum
        FROM mv1_tbl3_tbl4;

If a layer undergoes a full refresh, the nested materialized views that depend on it typically need to be fully refreshed again before incremental refreshes can proceed (see the documentation on creation and refreshes).

Materialized view logs and FAST refresh

If your business prioritizes refresh efficiency over recalculating all data every time, you can consider using FAST refresh. This method records changes to the base table and tries to process only incremental data during refresh.

CREATE TABLE IF NOT EXISTS tbl5 (col1 INT PRIMARY KEY, col2 INT, col3 INT);

CREATE MATERIALIZED VIEW mv_tbl5
    REFRESH FAST
    AS SELECT
           col2,
           SUM(col3) sum_col3
       FROM tbl5
       GROUP BY col2;

CALL DBMS_MVIEW.REFRESH('mv_tbl5');

CALL DBMS_MVIEW.REFRESH('mv_tbl5', 'c');

Real-time materialized view

For scenarios where query timeliness is critical and near-real-time results are desired, but frequent proactive refreshes are not feasible, you can evaluate whether to use a real-time materialized view based on your actual needs. This approach trades off query-time computation for higher data freshness.

CREATE TABLE IF NOT EXISTS tbl2(col1 INT, col2 INT, col3 INT);

CREATE MATERIALIZED VIEW mv_tbl2_rt
    ENABLE ON QUERY COMPUTATION
    AS SELECT
           col1
       FROM tbl2
       GROUP BY col1;

Query rewrite (ENABLE QUERY REWRITE)

If you want to minimize changes to your business SQL while allowing the optimizer to automatically use materialized view results, you can combine this with query rewrite. Query rewrite is suitable for fixed analytical scenarios, but whether a rewrite is successful depends on whether the query conditions match those defined in the materialized view.

CREATE TABLE IF NOT EXISTS test_tbl1 (col1 INT, col2 INT, col3 INT);

CREATE TABLE IF NOT EXISTS test_tbl2 (col1 INT, col2 INT, col3 INT);

CREATE MATERIALIZED VIEW mv_test_tbl1_tbl2
    ENABLE QUERY REWRITE
    AS SELECT
           t1.col1 col1,
           t1.col2 t1col2,
           t1.col3 t1col3,
           t2.col2 t2col2,
           t2.col3 t2col3
    FROM test_tbl1 t1, test_tbl2 t2
    WHERE t1.col1 = t2.col1;

SET query_rewrite_enabled = 'force';

SELECT
    COUNT(*),
    test_tbl1.col1 col1
FROM test_tbl1, test_tbl2
WHERE test_tbl1.col1 = test_tbl2.col1
AND test_tbl2.col2 > 10
GROUP BY col1;

Scenario 3: File data import and periodic refresh

When data comes from files rather than online business tables, you can first read file data through an external table, then create a full refresh materialized view based on that external table. This approach is suitable for periodic updates, offline synchronization queries, and regular data ingestion requirements in lakehouse integrated scenarios.

Starting from V4.3.5 BP2, you can create full refresh materialized views based on external tables as the base table. You must configure secure_file_priv, prepare the external file, and execute CREATE EXTERNAL TABLE (for complete steps, see Create a materialized view — Based on an external table).

CREATE EXTERNAL TABLE ext_tbl1 (
    id INT,
    name VARCHAR(50),
    c_date DATE
)
LOCATION = '/home/admin'
FORMAT = (
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
PATTERN = 'ext_tbl1.csv';

CREATE MATERIALIZED VIEW mv_ext_tbl1
    REFRESH COMPLETE
    AS SELECT * FROM ext_tbl1;

For more information about external tables, see: Import data using an external table and Catalogs and external tables.

References

• Accelerate materialized view queries
• Create a materialized view (MySQL)
• Refresh a materialized view (MySQL)
• DBMS_MVIEW.REFRESH