Open topic with navigation
CREATE VIEW — define a new view
CREATE [OR REPLACE] VIEW [
column_name]... ) ] AS SELECT
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
If a schema name is specified (for example,
CREATE VIEW ) 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.
DROP VIEW to drop views (See
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 ...
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.
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.
Name (optionally schema-qualified) of a view to be created.
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
column_name defined for a view must be unique.
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
column_list names (see Example 1).
If joining multiple tables, and the
column_list includes a column that is common to multiple joined tables, the column in the
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
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.
Note: 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
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.
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>
SELECT * FROM system.view_tables WHERE viewname='PLAYER_STATS';==================================== Row #1 ==================================== VIEWNAME: PLAYER_STATS VIEWSCHEMA: HOCKEY SEQUENCE: 0 TABLENAME: HOCKEY SCHEMA: HOCKEY ==================================== Row #2 ==================================== VIEWNAME: PLAYER_STATS VIEWSCHEMA: HOCKEY SEQUENCE: 1 TABLENAME: PLAYERS SCHEMA: HOCKEY
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
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 ...