CREATE VIEW — define a new view


CREATE 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). To change a view, drop and recreate it.

A view cannot be created on a temporary table.

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.