CREATE MATERIALIZED VIEW

Function

CREATE MATERIALIZED VIEW defines a materialized view for query. When this command is delivered, the query is executed and used to fill the view (unless WITH NO DATA is used), and the REFRESH MATERIALIZED VIEW command can be used to refresh data.

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, but it remembers the query used to initialize the view, so it can refresh data later. A materialized view has many attributes that are the same as those of a table, but does not support temporary materialized views.

Precautions

• Materialized views cannot be created on temporary tables or global temporary tables.
• After a materialized view is created, you need to run the REFRESH command to synchronize the materialized view with the base table when the data in the base table changes.

Syntax

CREATE MATERIALIZED VIEW mv_name
    [ ( column_name [, ...] ) ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
      [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ];

Parameter Description

• mv_name

  Specifies the name (optionally schema-qualified) of the materialized view to be created.

  Value range: a string. It must comply with the naming convention.

• column_name

  Specifies a column name in the new materialized view. The materialized view supports specified columns. The number of specified columns must be the same as the number of columns in the result of the subsequent query statement. If no column name is provided, the column name is obtained from the output column name of the query.

  Value range: a string. It must comply with the naming convention.

• WITH ( storage_parameter [= value] [, … ] )

  Specifies an optional storage parameter for a table or an index. CREATE MATERIALIZED VIEW supports all parameters supported by CREATE TABLE ,except OIDS. For details, see CREATE TABLE.

• TABLESPACE tablespace_name

  Tablespace to which the new materialized view belongs. If not specified, the default tablespace is used.

• AS query

  Specifies the SELECT, TABLE, or VALUES command. This query will be run in a security-constrained operation.

• [ WITH [ NO ] DATA ]

  Specifies whether the data produced by the query should be copied to the new materialized view. By default, the data will be copied. If the value NO is used, no data is copied.

Examples

-- Create the tpcds.store_returns table.
mogdb=# CREATE TABLE tpcds.store_returns
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    sr_item_sk                VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER
);
-- Create the materialized view tpcds.store_returns_mv based on the tpcds.store_returns base table.
mogdb=# CREATE MATERIALIZED VIEW tpcds.store_returns_mv AS SELECT * FROM tpcds.store_returns';

-- Use tpcds.store_returns to copy a new table tpcds.store_returns_mv2 as a materialized view.
mogdb=# CREATE MATERIALIZED VIEW tpcds.store_returns_mv2 AS table tpcds.store_returns;
-- Delete the table.
mogdb=# DROP MATERIALIZED VIEW tpcds.store_returns_mv cascade;