Practice scenarios of materialized views

This topic provides step-by-step SQL practice in MySQL mode (such as orders for beginners, tbl* for small fragments, and external tables for full MVs). You can follow the sections in order. Use this topic in conjunction with the materialized view query acceleration topic, which contains sales and items examples. If you need a complete walkthrough of the business tables, please read this topic first.

Build a real-time data warehouse with materialized views: scenarios and considerations

To make quick decisions during the construction of a real-time data warehouse, you can select the solution based on the order of data freshness requirements, computational complexity, and data source types.

The recommended order of practices to apply is:

1. Define the metrics and refresh strategy: Specify whether it's "minute-level incremental" or "real-time calculation at query time".
2. Rescheduling refresh mechanism : Evaluate whether FAST meets the requirements. If not, use the COMPLETE refresh method.
3. Finally, optimize the query side by adding columnar storage, indexes, and query rewriting only when needed to avoid introducing too many variables at once.

Try it out: Full refresh and incremental refresh on order tables

Let's take a orders table as an example. This example demonstrates how to create a materialized view with default full maintenance, an incremental view using FAST, and basic refresh methods, including manual refresh and scheduled refresh (as described in Refresh a materialized view).

Create the tables, insert data, and create a full-mapping 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 major compaction will be performed first. If the refresh strategy is not explicitly specified, a full refresh is usually required to keep the materialized view in sync with the base table.

Manually refresh (DBMS_MVIEW.REFRESH)

The materialized result doesn't change automatically until you trigger a refresh after you write to the base table.

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

SELECT * FROM mv1;

Return result:

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

You can see that the materialized view results are not changing automatically. You need to execute the following SQL trigger to refresh it:

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' means complete refresh; other parameters such as refresh_parallel follow the specifications in DBMS_MVIEW.REFRESH.

Query the refresh run status (example):

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

Incremental refresh

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. Perform manual refresh.

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

3. Query data in the materialized view.

   SELECT region, sum_price FROM mv1;
   

   You can query the DBA_SCHEDULER_JOBS views to find the background refresh jobs created with the START WITH ... NEXT ... clause (actual fields depend on the version of the database).

Object clean-up order

When you drop a materialized view or base table, you must follow the procedure of first dropping the materialized view, then dropping the base table.

DROP MATERIALIZED VIEW mv1;

DROP TABLE orders;

Scenario 1: Operation analysis and report query

In operational analytics and report querying scenarios, common issues include detailed tables with many columns, large scan ranges, and numerous changing query conditions.

These scenarios are typically read-heavy, making them ideal for reducing unnecessary column scans using columnar materialized views and further optimizing query performance with indexes and query rewriting when needed.

This section provides an example of using a column store materialized view 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;

For query rewriting, typically the rewritten condition must be able to utilize an index on the materialized view (for details, see Query Rewriting):

CREATE INDEX idx1_mv_ec_tbl1 ON mv_ec_tbl1(col1);

Scenario 2: Real-time metric calculation and query

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

These scenarios are typically not covered by a single capability but require the combination of FAST refresh, real-time materialized views, and query rewriting based on business timeliness requirements.

FAST refresh

If your business prioritizes refresh efficiency over recalculating all data every time, consider using FAST refresh. This method records changes in the base table and processes only incremental data during refreshes.

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 views

For scenarios requiring higher query timeliness and nearly real-time results, but not suitable for frequent manual refreshes, assess whether to use real-time materialized views. This approach exchanges 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 rewriting (ENABLE QUERY REWRITE)

If you want to minimize changes to business SQL while allowing the optimizer to automatically use materialized view results, combine this with query rewriting. Query rewriting is suitable for fixed analysis scenarios, but whether it can be applied depends on whether the query conditions match the materialized view definitions.

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 scheduled refresh

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

Starting from V4.3.5 BP2, you can create full-refresh materialized views with an external table as the base. First, 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 Overview of external tables.

References

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