Help Center/ Data Warehouse Service/ Developer Guide/ Developer Guide (9.1.1.x)/ DWS Materialized Views/ Materialized View Refresh/ Incremental Refresh
Updated on 2026-07-02 GMT+08:00
View PDF
Share

Incremental Refresh

Refresh Principles

Incremental refresh processes only the incremental data since the last refresh. It involves much less computation and consumes fewer resources than full refresh.

Incremental refresh syntax:

1
2
3
4
5
 
CREATE MATERIALIZED VIEW test_mv2
REFRESH FAST EVERY (interval '00:10:00') -- Trigger a refresh every 10 minutes.
ENABLE QUERY REWRITE -- Enable query rewriting.
WITH(orientation=COLUMN, enable_hstore_opt=ON) -- Use the column-store HStore Opt format.
AS Your_Select;

Constraints

  • Prerequisites for incremental refresh: Binlog must be enabled for base tables, and incremental calculation is required.
  • If any of the following conditions occurs, the system automatically switches the incremental refresh to the full refresh:
    • Binlog consumption records are cleared.
    • There are DDL operations in base tables.
    • Data is redistributed during scale-out.
    • Full data is required.
  • Supported incremental operators:
    • Aggregation: SUM/COUNT, AVG, and MIN/MAX
    • JOIN: INNER JOIN and OUTER JOIN
    • Others: HAVING, WHERE, and GROUP BY
    • Not supported: Subquery, UNION ALL, Window, and CTE

Impact on Services

  • Binlog must be enabled for base tables to record incremental data. Therefore, extra storage overhead is required.
  • The calculated incremental data is finally written to materialized views using INSERT, DELETE, or MERGE operations. Queries on the materialized views are not affected.

Scenarios

  • High requirement for real-time performance (ranging from minutes to seconds)
  • High change frequency of base tables but small change volume (< 30%)
  • Refresh frequency: by minute or by second (based on resource usage)
Parent Topic: Materialized View Refresh