CREATE VIEW

The CREATE VIEW statement creates a view of the tables specified in the SelectQuery clause. A view is a logical table that is based on one or more detail tables. The view itself contains no data. It is sometimes called a nonmaterialized view to distinguish it from a materialized view, which does contain data that has already been calculated from detail tables.

In a replicated environment for an active standby pair, if DDL_REPLICATION_LEVEL is 3 or greater when you execute CREATE VIEW on the active database, the view is replicated to all databases in the replication scheme. See Making DDL Changes in an Active Standby Pair in the Oracle TimesTen In-Memory Database Replication Guide for more information.

Required Privilege

The user executing the statement must have the CREATE VIEW privilege (if owner) or CREATE ANY VIEW (if not the owner) for another user's view.

The owner of the view must have the SELECT privilege on the detail tables.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

CREATE VIEW [Owner.]ViewName AS SelectQuery

Parameters

Parameter Description

[Owner.]ViewName

Name of view

SelectQuery

Selects column from the detail tables to be used in the view.

You can also create indexes on the view.

Restrictions on the SELECT Query

There are several restrictions on the query that is used to define the view.

  • A SELECT * query in a view definition is expanded when the view is created. Any columns added after a view is created do not affect the view.

  • Do not use the following in a SELECT statement that is used to create a view:

    • FIRST

    • ORDER BY

      If used, this is ignored by CREATE VIEW. The result will not be sorted.

    • Arguments

  • Each expression in the select list must have a unique name. A name of a simple column expression would be that column's name unless a column alias is defined. ROWID is considered an expression and needs an alias.

  • Do not use SELECT FOR UPDATE to create a view.

  • Certain TimesTen query restrictions are not checked when a non-materialized view is created. Views that violate those restrictions may be allowed to be created, but an error is returned when the view is referenced later in an executed statement.

  • When a view is referenced in the FROM clause of a SELECT statement, its name is replaced by its definition as a derived table at parsing time. If it is not possible to merge all clauses of a view to the same clause in the original select query to form a supported query without the derived table, the content of this derived table is materialized. For example, if both the view and the referencing select specify aggregates, the view is materialized before its result can be joined with other tables of the select.

  • Use the DROP VIEW statement to drop a view.

  • A view cannot be altered with an ALTER TABLE statement.

  • Referencing a view can fail because of dropped or altered detail tables.

Examples

Create a nonmaterialized view from the employees table. 

Command> CREATE VIEW v1 AS SELECT employee_id, email FROM employees;
Command> SELECT FIRST 5 * FROM v1;
< 100, SKING >
< 101, NKOCHHAR >
< 102, LDEHAAN >
< 103, AHUNOLD >
< 104, BERNST >
5 rows found.

Create a nonmaterialized view tview with column max1 from an aggregate query on the table t1. 

CREATE VIEW tview (max1) AS SELECT MAX(x1) FROM t1;

See also