AnalyticDB:Manage materialized views

When to use materialized views

Use a materialized view when all of the following are true:

• The query is expensive to run (complex joins, aggregations, or cross-system lookups).

• Results are read far more often than the underlying data changes.

• Slightly stale data is acceptable for your use case.

Stick with a regular view or query the base tables directly when:

• The underlying data changes frequently and readers always need the latest values.

• The query is lightweight and re-running it carries no meaningful cost.

Use cases

• Slow-changing aggregations: Summarize historical sales or usage metrics into a materialized view and refresh it nightly. Dashboards query the view instead of scanning raw tables.

• Complex recurring queries: Pre-compute expensive joins or window functions that multiple applications run repeatedly.

• External data acceleration: Create a materialized view over Object Storage Service (OSS) external tables or MaxCompute to pull external data into local storage, then create indexes on the view for fast lookups.

Create a materialized view

Example: Create a materialized view that filters the people table to rows where age > 40, distributed by id.

CREATE MATERIALIZED VIEW my_materialized_view AS
    SELECT * FROM people WHERE age > 40
    DISTRIBUTED BY (id);

SELECT * FROM my_materialized_view ORDER BY age;

Result:

    id     |  name  |   city   | age
-----------+--------+----------+-----
 004       | zhaoyi | zhenzhou |  44
 005       | xuliui | jiaxing  |  54
 006       | maodi  | shanghai |  55
(3 rows)

The query defined in the CREATE MATERIALIZED VIEW statement is used only to populate the view initially. The only structural difference between a materialized view and a table is that object identifiers (OIDs) are not auto-generated.

The DISTRIBUTED BY clause is optional. If omitted, the first column is used as the distribution key.

Refresh a materialized view

Syntax:

REFRESH MATERIALIZED VIEW view_name
    [ WITH [ NO ] DATA ]

Materialized view data is not updated automatically when base tables change. Run REFRESH MATERIALIZED VIEW to pull in the latest data.

Example: Insert a new row and observe that the view still returns old data. Then refresh.

INSERT INTO people VALUES('007','sunshen','shenzhen',60);

-- View still shows pre-refresh data
SELECT * FROM my_materialized_view ORDER BY age;

Result:

    id     |  name  |   city   | age
-----------+--------+----------+-----
 004       | zhaoyi | zhenzhou |  44
 005       | xuliui | jiaxing  |  54
 006       | maodi  | shanghai |  55
(3 rows)

REFRESH MATERIALIZED VIEW my_materialized_view;

SELECT * FROM my_materialized_view ORDER BY age;

Result after refresh:

    id     |  name   |   city   | age
-----------+---------+----------+-----
 004       | zhaoyi  | zhenzhou |  44
 005       | xuliui  | jiaxing  |  54
 006       | maodi   | shanghai |  55
 007       | sunshen | shenzhen |  60
(4 rows)

REFRESH MATERIALIZED VIEW my_materialized_view WITH NO DATA;

SELECT * FROM my_materialized_view ORDER BY age;
-- ERROR:  materialized view "my_materialized_view" has not been populated
-- HINT:  Use the REFRESH MATERIALIZED VIEW command.

REFRESH MATERIALIZED VIEW my_materialized_view;

SELECT * FROM my_materialized_view ORDER BY age;

    id     |  name   |   city   | age
-----------+---------+----------+-----
 004       | zhaoyi  | zhenzhou |  44
 005       | xuliui  | jiaxing  |  54
 006       | maodi   | shanghai |  55
 007       | sunshen | shenzhen |  60
(4 rows)

Drop a materialized view

Syntax:

DROP MATERIALIZED VIEW view_name [ CASCADE ]

Example: Drop a standalone materialized view.

CREATE MATERIALIZED VIEW depend_materialized_view AS
    SELECT * FROM my_materialized_view WHERE age > 50
    DISTRIBUTED BY (id);

DROP MATERIALIZED VIEW depend_materialized_view;

-- This fails because depend_materialized_view depends on my_materialized_view
DROP MATERIALIZED VIEW my_materialized_view;
-- ERROR:  cannot drop materialized view my_materialized_view because other objects depend on it
-- DETAIL:  materialized view depend_materialized_view depends on materialized view my_materialized_view
-- HINT:  Use DROP ... CASCADE to drop the dependent objects too.

-- CASCADE drops my_materialized_view and depend_materialized_view in one operation
DROP MATERIALIZED VIEW my_materialized_view CASCADE;

Usage notes