CREATE MATERIALIZED VIEW

Use the CREATE MATERIALIZED VIEW command to create or replace a sorted, projected, materialized view of a subset of the base tables columns. Each of these types of view projects a subset of the base table columns and is sorted on a specific set of the base table columns. The system persistently stores the view. It can be displayed by issuing the \dm command.

Syntax

Syntax for creating a materialized view:
CREATE [OR REPLACE] MATERIALIZED VIEW <viewname> AS SELECT <col>[,<col>…]
FROM <table> [ORDER BY <order_col>[,<order_col>…]];

Inputs

The CREATE MATERIALIZED VIEW command takes the following inputs:

Table 1. CREATE MATERIALIZED VIEW inputs
Input Description
OR REPLACE Replace an existing materialized view. Use this option to rebuild a view after its base table changes in some way or after you rename a database.
<viewname> The name of the materialized view to be created.
<col> The column or columns that comprise this view. You can specify up to 64 columns.
<table> The table from which the view is created.
<order_col> The column or columns on which to sort.

Restrictions

The CREATE MATERIALIZED VIEW command has the following restrictions:
  • You can only specify a single base table in the FROM clause.
  • You cannot use the WHERE clause when you create a materialized view.
  • The columns that you specify in the selection list must exist in the base table.
  • You must specify at least one column in the selection list.
  • You can select up to 64 columns for your materialized view.
  • The columns in the ORDER BY list must be specified in the selection list.
  • You cannot specify an external, temporary, system, or a clustered base table (CBT) as a base table for the view.

Outputs

The CREATE MATERIALIZED VIEW command has the following outputs:

Table 2. CREATE MATERIALIZED VIEW outputs
Output Description
CREATE MATERIALIZED VIEW The command was successful.
ERROR: Relation 'view' already exists. A view with the specified name already exists in the database.

Privileges

You must be the admin user, or the owner of the database or schema where the materialized view is defined, or your account must have the Create Materialized View privilege. If you specify the OR REPLACE option, you must also have Alter privilege for thematerialized view or for the Materialized View object class.

Usage

The following provides sample usage.
  • Create a materialized view:
    MYDB.SCH1(USER)=> CREATE MATERIALIZED VIEW kinds AS SELECT t1 FROM 
emp ORDER BY name;