Skip to main content

CREATE MATERIALIZED VIEW

Creates a materialized view.

Syntax​

CREATE MATERIALIZED VIEW [ IF  NOT EXISTS ] <table_name>
    [ (<column_name> [, ...] ) ]
    [ USING <method> ]
    [ WITH ( <storage_parameter> [= <value>] [, ... ] ) ]
    AS <query>
    [ WITH [ NO ] DATA ]
    [DISTRIBUTED {| BY <column> [<opclass>], [ ... ] | RANDOMLY | REPLICATED }]
    [ SYNC | ASYNC ]

Description​

After you use CREATE MATERIALIZED VIEW to create a materialized view for a query, the query is run and used to populate the view when the command is issued, unless WITH NO DATA is specified. The materialized view can be refreshed by REFRESH MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS. The difference is that CREATE MATERIALIZED VIEW records the query used to initialize the view. This capability allows materialized views to be freshed.

A materialized view has the same properties as a table but there is no support for temporary materialized views.

Parameters​

  • IF NOT EXISTS

    If you include IF NOT EXISTS in your command, no error will be reported when an existing materialized view with the same name exists. A notice will be issued instead.

  • <table_name>

    The name of the materialized view. You can specify the name with the schema qualification.

  • <column_name>

    The name of the column in the materialized view.

    In a materialized view, the column names are assigned based on position. The first column name is assigned to the first column of the query result, and so on. If no column name is specified, the output column names of the query are used.

  • USING <method>

    The clause that specifies the method to store the contents for the new materialized view. The method must be an access method of type TABLE.

    If you omit this clause, the default table access method is used.

  • WITH ( <storage_parameter> [= <value>] [, ... ]

    This clause specifies optional storage parameters for the materialized view. All parameters supported for CREATE TABLE are also supported for CREATE MATERIALIZED VIEW. See CREATE TABLE for more information.

  • <query>

    The query, which can be a SELECT, TABLE, or VALUES command. Executing this query can result in a security-restricted operation, especially if the query calls functions that create temporary tables. In such cases, the query will fail.

    Restrictions on base tables

    The tables specified in the query as the base tables of the materialized view do not support the following operations:

    • Rename the table.
    • Rename any columns in the table.
    • Add columns to the table.
    • Remove columns from the table.

  • WITH [NO] DATA

    Specifies whether to populate the materialized view with data.

    • WITH DATA specifies to populate data and is the default.

    • WITH NO DATA specifies not to populate data. This leaves the materialized view in an unscannable state.

    A materialized view declared with WITH NO DATA cannot be queried until it is populated with data by REFRESH MATERIALIZED VIEW.

  • DISTRIBUTED {| BY <column> [<opclass>], [ ... ] | RANDOMLY | REPLICATED }

    The distribution policy for the materialized view data.

    For details about each policy, see CREATE TABLE.

  • SYNC | ASYNC

    Specifies whether to automatically update the materialized view when the base tables are updated.

    • SYNC: yes.

    • ASYNC: no and is the default.

Usage notes​

Materialized views are read only. The system will not allow an INSERT, UPDATE, or DELETE on a materialized view. Use REFRESH MATERIALIZED VIEW to update the materialized view data.

If you want the data to be ordered upon generation, you must use an ORDER BY clause in the materialized view query. However, if a materialized view query contains an ORDER BY or SORT clause, the data is not guaranteed to be ordered or sorted if SELECT is performed on the materialized view.

Examples​

Create a view consisting of all comedy films:

CREATE MATERIALIZED VIEW comedies AS SELECT * FROM films 
WHERE kind = 'comedy';

This will create a view containing the columns that are in the film table at the time of view creation. Though * was used to create the materialized view, columns added later to the table will not be part of the view. Create a view that gets the top ten ranked baby names:

CREATE MATERIALIZED VIEW topten AS SELECT name, rank, gender, year FROM 
names, rank WHERE rank < '11' AND names.id=rank.id;

SQL standard compatibility​

CREATE MATERIALIZED VIEW is a Relyt extension.