Manage materialized views - AnalyticDB - Alibaba Cloud Documentation Center

Materialized views are similar to views and allow you to save frequently used or complex queries. Materialized views are different from views in that materialized views are based on physical storage. You cannot write data to materialized views. When a query accesses a materialized view, the system returns the data that is stored in the materialized view. Data in materialized views is not automatically updated and may become obsolete. However, you can retrieve data stored in materialized views faster than retrieving the same data by using base tables or views of the base tables. Therefore, materialized views have significant performance advantages if you accept periodic data updates.

Create a materialized view

Execute the CREATE MATERIALIZED VIEW statement to create a materialized view.

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

SELECT * from my_materialized_view ORDER BY age;

The following result is returned:

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

The query defined in a materialized view is used only to populate the materialized view. The only difference between a materialized view and a table is that object identifiers (OIDs) are not automatically generated in a materialized view. The DISTRIBUTED BY clause is optional when you create a materialized view. If the DISTRIBUTED BY clause is not specified, the first column of the table is used as the distribution key.

Note

If a materialized view query contains an ORDER BY or SORT clause, the data may not be ordered or sorted.

Refresh or disable a materialized view

Execute the REFRESH MATERIALIZED VIEW statement to update data in a materialized view.

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

SELECT * from my_materialized_view ORDER BY age;

The following result is returned:

     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;

The following result is returned:

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

If you include the WITH NO DATA clause in the REFRESH MATERIALIZED VIEW statement, the materialized view is not populated with data and cannot be scanned after the statement is executed. If a query attempts to access a materialized view that cannot be scanned, an error is returned.

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;

The following result is returned:

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

Delete a materialized view

Execute the DROP MATERIALIZED VIEW statement to delete a 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;

DROP MATERIALIZED VIEW ... CASCADE allows you to delete all objects that depend on the materialized view. If the materialized view that you want to delete has dependent views, the materialized views are also deleted.

Important

You must specify the CASCADE option in the DROP MATERIALIZED VIEW statement when you delete a materialized view that has dependent views. Otherwise, an error is returned.

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

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.

DROP MATERIALIZED VIEW my_materialized_view CASCADE;

Scenarios

Materialized views can be used for queries that are not time-sensitive.
Materialized views can be used for frequently used or complex queries.
To implement fast queries and analysis, you can create materialized views based on external data sources, such as the external tables of Object Storage Service (OSS) or MaxCompute. You can use the materialized views to store external data to on-premises storage. You can also create indexes for the materialized views.