CREATE VIEW

CREATE VIEW — define a new view

Syntax

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

Description

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 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.

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 ...

In the event that a user-defined function is called by an expression-based index, any attempt to drop or modify the function will complete with an error warning but the index will not be recreated.

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.

User-defined parameters are not allowed in a CREATE VIEW statement.

Parameters

schema: Name of the schema in which the view will be created. The default is the current schema as defined by the USE command. If schema is provided, and the schema does not exist, it will be created. Views cannot be created in the SYSTEM schema. This schema is reserved for database metadata.
view: Name (optionally schema-qualified) of a view to be created.
column_name: An optional list of column names to be used as columns for the view. If specified, these names override the column names that would be used based on the SELECT query. Each column_name defined for a view must be unique.
query: A SELECT statement, see SELECT, which provides the columns and rows of the view. If the optional column_name list is not provided when creating the view, the column names defined for the view are the same as the SELECT statement column_list names, see Example 1.

If joining multiple tables, and the SELECT statement column_list includes a column that is common to multiple joined tables, the column in the SELECT statement column_list must be qualified by a table name or, if used, table name alias.

If the optional column_name list is not provided when creating the view, and the SELECT statement column_list includes duplicate column names, those duplicate names must be assigned alias column names, using AS. All column names defined in a view must be unique.

Using an ORDER BY in a VIEW definition is not recommended. To improve performance, the NuoDB optimizer will ignore ordering of the result set returned by the VIEW, unless a LIMIT or OFFSET is supplied in the VIEW definition, along with the ORDER BY, see Example 2. Let’s say you have defined a VIEW with an ORDER BY and now you want to SELECT from the VIEW and order the results differently. The execution plan for both of the ORDER BY statement would need to perform two sorts, one for the ORDER BY in the VIEW definition, and one for the ORDER BY for the SELECT statement. However, because the NuoDB optimizer eliminates the ORDER BY in the VIEW definition, the execution plan for this SELECT statement requires only one sort, which results in a performance improvement.

Examples

Example 1: Create a view without column list

SELECT * FROM hockey.hockey where name like '%BERG%';
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
  1    37    PATRICE BERGERON   Forward  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins

SELECT playerid, firstname, lastname, height, weight, position, birthcountry
  FROM hockey.players
 WHERE lastname LIKE 'Bergeron%';
 PLAYERID   FIRSTNAME  LASTNAME  HEIGHT  WEIGHT  POSITION  BIRTHCOUNTRY
 --------- ----------- --------- ------- ------- --------- -------------
 bergeje01 Jean-Claude Bergeron    74      192       G        Canada
 bergema02 Marc-Andre  Bergeron    70      197       D        Canada
 bergemi01 Michel      Bergeron    70      170       R        Canada
 bergepa01 Patrice     Bergeron    72      186       C        Canada
 bergeyv01 Yves        Bergeron    69      165       R        Canada


CREATE VIEW player_stats  AS
SELECT number, name
            , hockey.position
            , team, height, weight, birthcountry
  FROM hockey.hockey
    JOIN hockey.players ON name = UPPER(CONCAT(firstname, ' ', lastname));

SELECT * FROM player_stats WHERE name LIKE '%BERGE%';
 NUMBER        NAME       POSITION   TEAM  HEIGHT  WEIGHT  BIRTHCOUNTRY
 ------- ---------------- --------- ------ ------- ------- -------------
   37    PATRICE BERGERON  Forward  Bruins   72      186      Canada

SET OUTPUT VERTICAL;
SELECT * FROM system.tables WHERE tablename='PLAYER_STATS';
==================================== Row #1 ====================================
TABLENAME: PLAYER_STATS
SCHEMA: HOCKEY
EXTENDS: <null>
TYPE: VIEW
SUBTYPE: 0
TABLEID: 88
CURRENTVERSION: <null>
REMARKS: <null>
VIEWDEFINITION: VIEWDEF VER='2.0' SCHEMA="HOCKEY"
CREATE VIEW player_stats  AS SELECT number, name             , hockey.position               ,
team, height, weight, birthcountry   FROM hockey.hockey
JOIN hockey.players ON name = UPPER(CONCAT(firstname, ' ', lastname));

CARDINALITY: <null>
RECORDSBATCHFACTOR: <null>

Example 2: Create view using a column name list

CREATE VIEW vw_team_rooster
    (team_name, year, conference, division, rank, position
   , player_name)
AS
SELECT t.name, t.year, t.conferenceid, t.divisionid, t.rank, s.position
   , p.lastname||','||p.firstname
  FROM teams t
    JOIN scoring s ON s.year = t.year and s.teamid = t.teamid
    JOIN players p ON p.playerid = s.playerid
 WHERE t.rank = 1
  ORDER BY year desc, conferenceid, divisionid, position;

SELECT * FROM vw_team_rooster
  WHERE year = '2011' AND conference = 'EC' AND division = 'NE';
   TEAM_NAME   YEAR  CONFERENCE  DIVISION  RANK  POSITION     PLAYER_NAME
 ------------- ----- ----------- --------- ----- --------- -----------------
 Boston Bruins 2011      EC         NE       1       C     Peverley,Rich
 Boston Bruins 2011      EC         NE       1       C     Sauve,Max
 Boston Bruins 2011      EC         NE       1       C     Rolston,Brian
 Boston Bruins 2011      EC         NE       1       C     Seguin,Tyler
 Boston Bruins 2011      EC         NE       1       C     Krejci,David
 Boston Bruins 2011      EC         NE       1       C     Kelly,Chris
 Boston Bruins 2011      EC         NE       1       C     Horton,Nathan
 Boston Bruins 2011      EC         NE       1       C     Marchand,Brad

Example 3: Ordering the results for a VIEW: For performance reasons, the NuoDB optimizer will ignore the ORDER BY in a view definition unless a LIMIT and / or OFFSET is also used.

DROP VIEW IF EXISTS vw_hockey;
CREATE VIEW vw_hockey
AS
SELECT id, number, name, position, team
  FROM hockey ORDER BY name;

SELECT * FROM vw_hockey;
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------

  1    37    PATRICE BERGERON   Forward  Bruins
  2    48    CHRIS BOURQUE      Forward  Bruins
  3    11    GREGORY CAMPBELL   Forward  Bruins
  4    18    NATHAN HORTON      Forward  Bruins
  5    23    CHRIS KELLY        Forward  Bruins
  6    46    DAVID KREJCI       Forward  Bruins
...

DROP VIEW IF EXISTS vw_hockey;
CREATE VIEW vw_hockey
AS
SELECT id, number, name, position, team
  FROM hockey
 ORDER BY name LIMIT 5;

SELECT * FROM vw_hockey;
 ID  NUMBER       NAME      POSITION   TEAM
 --- ------- -------------- --------- ------

 19    45    AARON JOHNSON   Defense  Bruins
 20    54    ADAM MCQUAID    Defense  Bruins
 17    21    ANDREW FERENCE  Defense  Bruins
 22    35    ANTON KHUDOBIN  Goalie   Bruins
  9    63    BRAD MARCHAND   Forward  Bruins
...