CREATE VIEW — define a new view


CREATE [OR REPLACE] VIEW [schema.]view [ ( column_name [ ,column_name ]... ) ] AS SELECT query


CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. The column_name list is optional. However, if the column_name list is provided, the number of columns in the column_name list must be identical to the number of columns in the SELECT query. If a schema name is specified (for example, CREATE VIEW myschema.myview ...) then the view is created in the specified schema; otherwise it is created in the current schema. The view name can be the same as names for sequences and indexes. However, the view name must be distinct from the name of any other view or table in the schema that the view is being created in. Use DROP VIEW to drop views (See DROP VIEW).

When the OR REPLACE clause is used for an existing view name, the effect is that of dropping the existing view and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a view with the specified name does not already exist.

A view cannot be created on a temporary table.

Note: When using CREATE OR REPLACE statements, and the same database object is created concurrently from two different transactions, the following error may be returned:

duplicate value in unique index ...

The CREATE VIEW command used to create each view is stored in SYSTEM.TABLES in the VIEWDEFINITION column. Any tables used in view definitions are stored in SYSTEM.VIEW_TABLES. See TABLES System Table Description, VIEW_TABLES System Table Description, and Example1 below.

Caution: If DDL statements are to be executed concurrently with DML statements, ensure that a LOCK TABLE command is successfully executed prior to executing the DDL statement.