CREATE MATERIALIZED VIEW v13

Suggest edits

Name

CREATE MATERIALIZED VIEW -- define a new materialized view.

Synopsis

CREATE MATERIALIZED VIEW <name>
   [<build_clause>][<create_mv_refresh>] AS subquery

      Where <build_clause> is:

      BUILD {IMMEDIATE | DEFERRED}

      Where <create_mv_refresh> is:

      REFRESH [COMPLETE] [ON DEMAND]

Description

CREATE MATERIALIZED VIEW defines a view of a query that is not updated each time the view is referenced in a query. By default, the view is populated when the view is created; you can include the BUILD DEFERRED keywords to delay the population of the view.

A materialized view may be schema-qualified; if you specify a schema name when invoking the CREATE MATERIALIZED VIEW command, the view will be created in the specified schema. The view name must be distinct from the name of any other view, table, sequence, or index in the same schema.

Parameters

name

The name (optionally schema-qualified) of a view to be created.

subquery

A SELECT statement that specifies the contents of the view. Refer to SELECT for more information about valid queries.

build_clause

Include a build_clause to specify when the view should be populated. Specify BUILD IMMEDIATE, or BUILD DEFERRED:

  • BUILD IMMEDIATE instructs the server to populate the view immediately. This is the default behavior.
  • BUILD DEFERRED instructs the server to populate the view at a later time (during a REFRESH operation).

create_mv_refresh

Include the create_mv_refresh clause to specify when the contents of a materialized view should be updated. The clause contains the REFRESH keyword followed by COMPLETE and/or ON DEMAND, where:

  • COMPLETE instructs the server to discard the current content and reload the materialized view by executing the view's defining query when the materialized view is refreshed.

  • ON DEMAND instructs the server to refresh the materialized view on demand by calling the DBMS_MVIEW package or by calling the Postgres REFRESH MATERIALIZED VIEW statement. This is the default behavior.

Notes

Materialized views are read only - the server will not allow an INSERT, UPDATE, or DELETE on a view.

Access to tables referenced in the view is determined by permissions of the view owner; the user of a view must have permissions to call all functions used by the view.

For more information about the Postgres REFRESH MATERIALIZED VIEW command, see the PostgreSQL Core Documentation available at:

https://www.postgresql.org/docs/current/static/sql-refreshmaterializedview.html

Examples

The following statement creates a materialized view named dept_30:

CREATE MATERIALIZED VIEW dept_30 BUILD IMMEDIATE AS SELECT * FROM emp WHERE deptno = 30;

The view contains information retrieved from the emp table about any employee that works in department 30.

← Prev

CREATE INDEX

↑ Up

Database Compatibility for Oracle Developers SQL Guide

Next →

CREATE PACKAGE