Materialized Views

Overview

A materialized view is a special physical table. Unlike a view, a materialized view stores query results instead of query definitions. Compared with views, materialized views have a higher query speed. However, materialized views cannot be updated in real time and are not flexible for services that require real-time response. Materialized views are classified into fast-refresh materialized view and complete-refresh materialized view.

• Complete-refresh materialized view: When a complete-refresh materialized view needs to be refreshed, the view is fully updated by re-executing the query and replacing the result in the current view. The complete-refresh materialized views are suitable for scenarios with low data update frequency and high query costs, as the high cost of refreshing such views makes them unsuitable for frequent updates.
• Fast-refresh materialized view: allows incremental updates to a view, that is, only the data that has changed since the last refresh is updated. It applies to scenarios with high update frequency. Although the fast-refresh materialized view is cost-effective, it supports only a few scenarios (for example, aggregate functions are not supported). Currently, only simple filter query and base table UNION ALL are supported.

Examples

• Complete-refresh materialized view

  -- Create a table and insert data (complete-refresh materialized view supports only the Astore base table).
  gaussdb=# CREATE TABLE tb_test(pid varchar(5),snum varchar(5)) WITH (storage_type = astore);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'pid' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  gaussdb=# INSERT INTO tb_test VALUES ('00001','aaaaa');
  INSERT 0 1
  gaussdb=# INSERT INTO tb_test VALUES ('00001','aacba');
  INSERT 0 1
  gaussdb=# INSERT INTO tb_test VALUES ('00002','badbc');
  INSERT 0 1
  
  -- Create a complete-refresh materialized view.
  gaussdb=# CREATE MATERIALIZED VIEW mv_test AS SELECT pid,count(snum) cnt FROM tb_test GROUP BY pid;
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'pid' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  INSERT 0 2
  
  
  -- Query the complete-refresh materialized view.
  gaussdb=# SELECT * FROM mv_test;
    pid  | cnt 
  -------+-----
   00002 |   1
   00001 |   2
  (2 rows)
  
  -- Insert data, and then continue to query complete-refresh materialized view.
  gaussdb=# INSERT INTO tb_test VALUES ('00002','bdacc');
  INSERT 0 1
  -- The query result does not change. Manually refresh the materialized view.
  gaussdb=# SELECT * FROM mv_test;
    pid  | cnt 
  -------+-----
   00002 |   1
   00001 |   2
  (2 rows)
  
  
  -- Refresh the complete-refresh materialized view.
  gaussdb=# REFRESH MATERIALIZED VIEW mv_test;
  INSERT 0 2
  
  -- Query the complete-refresh materialized view.
  gaussdb=# SELECT * FROM mv_test;
    pid  | cnt 
  -------+-----
   00002 |   2
   00001 |   2
  (2 rows)
  
  
  -- Drop the table and materialized view.
  gaussdb=# DROP MATERIALIZED VIEW mv_test;
  DROP MATERIALIZED VIEW
  gaussdb=# DROP TABLE tb_test;
  DROP TABLE

• Fast-refresh materialized view

  -- Create a table.
  gaussdb=# CREATE TABLE tb_test(c1 int) WITH (storage_type = astore);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- An error is reported when the fast-refresh materialized view contains an aggregate function.
  gaussdb=# CREATE INCREMENTAL MATERIALIZED VIEW imv_test AS SELECT count(*) FROM tb_test;
  ERROR:  Feature not supported
  DETAIL:  aggregates on incremental materialized view creation
  
  -- Create a fast-refresh materialized view.
  gaussdb=# CREATE INCREMENTAL MATERIALIZED VIEW imv_test AS SELECT * FROM tb_test;
  CREATE MATERIALIZED VIEW
  
  -- Fastly refresh the fast-refresh materialized view.
  gaussdb=# REFRESH INCREMENTAL MATERIALIZED VIEW imv_test;
  REFRESH MATERIALIZED VIEW
  
  -- Completely refresh the fast-refresh materialized view.
  gaussdb=# REFRESH MATERIALIZED VIEW imv_test;
  REFRESH MATERIALIZED VIEW
  
  -- Drop the table and materialized view.
  gaussdb=# DROP MATERIALIZED VIEW imv_test;
  DROP MATERIALIZED VIEW
  gaussdb=# DROP TABLE tb_test;
  DROP TABLE