CREATE MATERIALIZED VIEW¶
Creates a new materialized view in the current/specified schema, based on a query of an existing table, and populates the view with data.
For more details, see Working with Materialized Views.
CREATE [ OR REPLACE ] [ SECURE ] MATERIALIZED VIEW [ IF NOT EXISTS ] <name> [ COPY GRANTS ] ( <column_list> ) [ <col1> [ WITH ] MASKING POLICY <policy_name> [ WITH ] TAG ( <tag_key> = 'tag_value' [ , <tag_key> = 'tag_value' , ... ] ) ] [ , <col2> [ ... ] ] [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ] [ WITH ] TAG ( <tag_key> = 'tag_value' [ , <tag_key> = 'tag_value' , ... ] ) ] [ COMMENT = '<string_literal>' ] [ CLUSTER BY ( <expr1> [, <expr2> ... ] ) ] AS <select_statement>
Specifies the identifier for the view; must be unique for the schema in which the view is created.
In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g.
"My object"). Identifiers enclosed in double quotes are also case-sensitive.
For more details, see Identifier Requirements.
There are limitations on the
select_statement. For details, see:
If you do not want the column names in the view to be the same as the column names of the underlying table, you may include a column list in which you specify the column names. (You do not need to specify the data types of the columns.)
If you include a CLUSTER BY clause for the materialized view, then you must include the column name list.
MASKING POLICY = policy_name
Specifies the masking policy to set on a column.
ROW ACCESS POLICY policy_name ON ( col_name [ , col_name ... ] )
Specifies the row access policy to set on the materialized view.
TAG ( tag_key = 'tag_value' [ , tag_key = 'tag_value' , ... ] ) ]
Specifies the tag name (i.e. the key) and the tag value.
The tag value is always a string and the maximum number of characters for the tag value is 256. The maximum number of tags that can be set on an object is 20.
Specifies a comment for the view. The string literal should be in single quotes. (The string literal should not contain single quotes unless they are escaped.)
Default: No value.
Specifies an expression on which to cluster the materialized view. Typically, each expression is the name of a column in the materialized view.
Specifies that the view is secure. For more information about secure views, see Working with Secure Views.
Default: No value (view is not secure)
If you are replacing an existing view by using the
OR REPLACEclause, then the replacement view retains the access permissions from the original view. This parameter copies all privileges, except OWNERSHIP, from the existing view to the new view. The new view does not inherit any future grants defined for the object type in the schema. By default, the role that executes the CREATE MATERIALIZED VIEW statement owns the new view.
If the parameter is not included in the CREATE VIEW statement, then the new view does not inherit any explicit access privileges granted on the original view but does inherit any future grants defined for the object type in the schema.
Note that the operation to copy grants occurs atomically with the CREATE VIEW statement (i.e. within the same transaction).
Default: No value (grants are not copied).
Creating a materialized view requires CREATE MATERIALIZED VIEW privilege on the schema, and SELECT privilege on the base table. For more information about privileges and materialized views, see Privileges on a Materialized View’s Schema.
When you choose a name for the materialized view, note that a schema cannot contain a table and view with the same name. CREATE [ MATERIALIZED ] VIEW produces an error if a table with the same name already exists in the schema.
When specifying the
select_statement, note the following:
You cannot specify a HAVING clause or an ORDER BY clause.
If you include a CLUSTER BY clause for the materialized view, you must include the
If you refer to the base table more than once in the
select_statement, use the same qualifier for all references for the base table.
For example, don’t use a mix of
database.schema.base_tablein the same
select_statement. Instead, choose one of these forms (e.g.
database.schema.base_table), and use this consistently throughout the
View definitions are not updated if the schema of the underlying source table is changed so that the view definition becomes invalid. For example:
A view is created referencing a specific column in a source table and the column is subsequently dropped from the table.
A view is created using
SELECT *from a table and any column is subsequently dropped from the table.
In either of these scenarios, querying the view returns an error.
If a source table for a materialized view is dropped, querying the view returns the following error:
Materialized View <view_name> is invalid.
OR REPLACEis the equivalent of using DROP MATERIALIZED VIEW on the existing materialized view and then creating a new view with the same name. The drop and create actions occur in a single atomic operation. This means that any queries concurrent with the CREATE OR REPLACE MATERIALIZED VIEW operation use either the old or new materialized view version.