Updated on 2025-09-19 GMT+08:00

Creating a Partitioned Materialized View

DWS allows you to create partitioned materialized views in the following scenarios:

  • Partitions are aligned proportionally. The partitioning conditions of materialized views are the same as those of base tables.
  • Partitions are aligned based on time granularity, for example, daily partitioning for the base table and monthly partitioning for the materialized view.
  • Partitions are aligned for multiple base tables with identical partition conditions, automatically mapping the base tables to the materialized view.
  • The base table has multiple partition keys and the first key is chosen as the partition key for creating a materialized view.

Syntax

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name
[ ( column_name [, ...] ) ]
[ BUILD { DEFERRED | IMMEDIATE } ]
[ REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ]
[ { ENABLE | DISABLE } QUERY REWRITE ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
[ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ]
AS query;

Constraints

  • You can only use range partitioning.
  • The materialized view only supports a single partition key, which must be the first partition key of the base table. The base table can have a combined partition key.
  • Rollup mode is supported only for time type, using date_trunc for rollup calculation. Supported rollup intervals include day, month, quarter, and year.
  • Columns that generate null values in outer joins cannot be used as partition keys.
  • disable full group by in MySQL compatibility mode is not supported.
  • Pre-aggregated materialized views cannot be used to create partitioned materialized views.
  • Query rewriting for a specified partition is not allowed.
  • The base table of the partitioned materialized view does not support SPLIT and MERGE operations.
  • When both sides of a union meet the partition constraint check of the materialized view, a materialized view bound to multiple tables is created.
  • If JOIN is used to join base tables and the partition key is set to a public column, a materialized view bound to multiple tables is created if the joined base tables meet the partition constraint detection of the materialized view.
  • Query statements cannot contain sublinks.
  • Query statements cannot contain subplans.
  • Data modification using DML and DDL statements is not allowed. Data can only be updated by refreshing.

Examples

Create fact tables fact_table and fact_table1.

1
2
3
4
5
6
7
CREATE TABLE fact_table (start_time TIMESTAMPTZ NOT NULL, id int)PARTITION BY RANGE(start_time)(
PARTITION p1 VALUES LESS THAN ('2024-01-01'),
PARTITION p2 VALUES LESS THAN ('2024-01-02'),
PARTITION p3 VALUES LESS THAN ('2024-02-03'),
PARTITION p4 VALUES LESS THAN ('2024-03-01'),
PARTITION p5 VALUES LESS THAN ('2024-03-21'),
PARTITION p6 VALUES LESS THAN ('2024-04-02'));
1
2
3
4
5
6
7
CREATE TABLE fact_table1 (start_time TIMESTAMPTZ NOT NULL, id int)PARTITION BY RANGE(start_time)(
PARTITION p1 VALUES LESS THAN ('2024-01-01'),
PARTITION p2 VALUES LESS THAN ('2024-01-02'),
PARTITION p3 VALUES LESS THAN ('2024-02-03'),
PARTITION p4 VALUES LESS THAN ('2024-03-01'),
PARTITION p5 VALUES LESS THAN ('2024-03-21'),
PARTITION p6 VALUES LESS THAN ('2024-04-02'));

Create a dimension table dimen_table.

1
CREATE TABLE dimen_table (start_time TIMESTAMPTZ NOT NULL, id INT);

Scenario 1: Align partitions proportionally.

Create the materialized view level_normal. Ensure that the partition of the materialized view matches that of the fact_table table, with identical partition conditions and no specific data type requirements.

1
2
3
4
CREATE MATERIALIZED VIEW level_normal enable query rewrite 
DISTRIBUTE BY HASH(start_time)
PARTITION BY start_time 
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

Scenario 2: Adjust the partitioning time granularity.

Use the date_trunc parameter on the partition key to create a materialized view with coarser partitioning time granularity compared to the base table. DWS updates the corresponding roll-up partition in the materialized view after detecting data changes in the base table partition.

You can use the date_trunc('time_unit', column_name) parameter to specify the roll-up targets. The materialized view partition covers a wider time range compared to the base table partition. For instance, if the base table is partitioned by day, the materialized view can be partitioned by month. The date_trunc parameter adjusts the time granularity in the order of day, quarter, year, or month. Supported data types include time, timestamp, and timestamptz.

1
2
3
4
CREATE MATERIALIZED VIEW level_month enable query rewrite 
DISTRIBUTE BY HASH(start_time)
PARTITION BY date_trunc('month', start_time)
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

Scenario 3: Align partitions for multiple base tables.

If multiple base tables use the same partition key type and partition range, you can create a materialized view bound to multiple base tables.

1
2
3
4
CREATE MATERIALIZED VIEW level_normal enable query rewrite
DISTRIBUTE BY HASH(start_time) 
PARTITION BY start_time
AS SELECT start_time, id FROM fact_table union SELECT start_time, id FROM fact_table1;
1
2
3
4
CREATE MATERIALIZED VIEW level_normal enable query rewrite
DISTRIBUTE BY HASH(start_time) 
PARTITION BY start_time
AS SELECT start_time, id FROM fact_table inner join fact_table1 on fact_table.start_time=dimen_table.start_time;

Scenario 4: When the base table has multiple partition keys, use the first partition key of the base table to create a materialized view.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CREATE TABLE IF NOT EXISTS  multi_time_base (
    new_start_time timestamp with time zone NOT NULL,
    id int,
    v1 int
) PARTITION BY RANGE(new_start_time,id) (
    PARTITION p1 VALUES LESS THAN ('2022-01-01',400),
    PARTITION p2 VALUES LESS THAN ('2022-01-05',400),
    PARTITION p3 VALUES LESS THAN ('2022-01-05',500),
    PARTITION p4 VALUES LESS THAN ('2022-02-15',500),
    PARTITION p5 VALUES LESS THAN ('2022-02-16',500),
    PARTITION p6 VALUES LESS THAN ('2022-03-10',500)
); 
CREATE MATERIALIZED VIEW mult_time_mv
PARTITION BY (date_trunc('month', new_start_time))
AS SELECT new_start_time,id FROM multi_time_base;