Oracle7 Server SQL Reference

CREATE VIEW

Purpose

To define a view, a logical table based on one or more tables or views.

Prerequisites

To create a view in your own schema, you must have CREATE VIEW system privilege. To create a view in another user's schema, you must have CREATE ANY VIEW system privilege.

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. For information on these privileges, see the SELECT command , the INSERT command , the UPDATE command , and the DELETE command . The owner must be granted these privileges directly, rather than through a role.

Syntax

Keywords and Parameters

OR REPLACE

recreates the view if it already exists. You can use this option to change the definition of an existing view without dropping, recreating, and regranting object privileges previously granted on it.

FORCE

creates the view regardless of whether the view's base tables exist or the owner of the schema containing the view has privileges on them. Note that both of these conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.

NOFORCE

creates the view only if the base tables exist and the owner of the schema containing the view has privileges on them.

The default is NOFORCE.

schema

is the schema to contain the view. If you omit schema, Oracle7 creates the view in your own schema.

view

is the name of the view.

alias

specifies names for the expressions selected by the view's query. The number of aliases must match the number of expressions selected by the view. Aliases must follow the rules for naming schema objects in the section, "Naming Objects and Parts," . Aliases must be unique within the view.

If you omit the aliases, Oracle7 derives them from the columns or column aliases in the view's query. For this reason, you must use aliases if the view's query contains expressions rather than only column names.

AS subquery

identifies columns and rows of the table(s) that the view is based on. A view's query can be any SELECT statement without the ORDER BY or FOR UPDATE clauses. Its select list can contain up to 254 expressions. See the syntax description of subquery .

WITH READ ONLY

specifies that no deletes, inserts, or updates can be performed through the view.

WITH CHECK OPTION

specifies that inserts and updates performed through the view must result in rows that the view query can select. The CHECK OPTION cannot make this guarantee if there is a subquery in the query of this view or any view on which this view is based.

CONSTRAINT

is the name assigned to the CHECK OPTION constraint. If you omit this identifier, Oracle7 automatically assigns the constraint a name of this form:

SYS_Cn

where

n is an integer that makes the constraint name unique within the database.

Usage Notes

A view is a logical table that allows you to access data from other tables and views. A view contains no data itself. The tables upon which a view is based are called base tables.

Views are used for the following purposes:

To provide an additional level of table security, by restricting access to a predetermined set of rows and/or columns of a base table.

To hide data complexity. For example, a view may be used to act as one table when actually several tables are used to construct the result.

To present data from another perspective. For example, views provide a means of renaming columns without actually changing the base table's definition.

To cause Oracle7 to perform some operations, such as joins, on the database containing the view, rather than another database referenced in the same SQL statement.

You can use a view anywhere you can use a table in any of the following SQL statements:

COMMENT

DELETE

INSERT

LOCK TABLE

UPDATE

SELECT

The View Query

For the syntax of the view's query, see the syntax description of subquery

. Note the following caveats:

A view's query cannot select the CURRVAL or NEXTVAL pseudocolumns.

If a view's query selects the ROWID, ROWNUM, or LEVEL pseudocolumns, they must have aliases in the view's query.

You can define a view with a query that uses an asterisk (*) to select all the columns of a table:

	  CREATE VIEW emp_vu
		   AS SELECT * FROM emp

Oracle7 translates the asterisk into a list of all the columns in the table at the time the CREATE VIEW statement is issued. If you subsequently add new columns to the table, the view will not contain these columns unless you recreate the view by issuing another CREATE VIEW statement with the OR REPLACE option. It is recommended that you explicitly specify all columns in the select list of a view query, rather than use the asterisk.

You can create views that refer to remote tables and views by using database links in the view query. It is recommended that any remote table or view referenced in the view query be qualified with the name of the schema containing it. It is recommended that any database links used in the view query be defined using the CONNECT TO clause of the CREATE DATABASE LINK command.

The above caveats also apply to the query for a snapshot.

If the view query contains any of the following constructs, you cannot perform inserts, updates, or deletes on the view:

set operators

group functions

GROUP BY, CONNECT BY, or START WITH clauses

the DISTINCT operator

Note that if a view contains pseudocolumns or expressions, you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.

Join Views

A join view is a view with a subquery containing a join. The restrictions described above also apply to join views.

If at least one column in the subquery join has a unique index, then it may be possible to modify one base table in a join view. You can query USER_UPDATABLE_COLUMNS to see whether the columns in a join view are updatable. For example:

CREATE VIEW ed AS
	SELECT e.empno, e.ename, d.deptno, d.loc
	  FROM emp e, dept d
	 WHERE e.deptno = d.deptno

View created.

SELECT column_name, updatable 
  FROM user_updatable_columns
 WHERE table_name = 'ED';

COLUMN_NAME     UPD
--------------- ---
ENAME           YES
DEPTNO          NO
EMPNO           YES
LOC             NO

In the above example, note that there is a unique index on the DEPTNO column of the DEPT table.

In the above example, you may insert, update or delete a row from the EMP base table because all the columns in the view mapping to the emp table are marked as updatable and because the primary key of emp is included in the view. For more information on updating join views, see "Modifying a Join View" in the Oracle7 Server Application Developer's Guide. If there were not null columns in the base EMP table that were not specified in the view subquery, then you could not insert into the table using the view.

Partition Views

A partition view is a view that for performance reasons brings together several tables to behave as one. The effect is as though a single table were divided into multiple tables (partitions) that could be independently accessed. Each partition contains some subset of the values in the view, typically a range of values in some column. Among the advantages of partition views are the following:

each table in the view is separately indexed, and all indexes can be scanned in parallel.

if Oracle can tell by the definition of a partition that it can produce no rows to satisfy a query, Oracle will save time by not examining that partition.

the partitions can be as sophisticated as can be expressed in CHECK constraints.

if you have the parallel query option, the partitions can be scanned in parallel.

partitions can overlap.

Among the disadvantages of partition views are the following:

they cannot be updated.

they have no master index; rather each component table is separately indexed. For this reason, they are recommended for DSS (Decision Support Systems or "data warehousing") applications, but not for OLTP.

To create a partition view, do the following:

CREATE the tables that will comprise the view or ALTER existing tables suitably.

give each table a constraint that limits the values it can hold to the range or other restriction criteria desired.

create a local index on the constrained column(s) of each table.

create the partition view as a series of SELECT statements whose outputs are combined using UNION ALL. The view should select all rows and columns from the underlying tables. For more information on SELECT or UNION ALL, see "SELECT" .

if you have the parallel query option enabled, specify that the view is parallel, so that the tables within it are accessed simultaneously when the view is queried. There are two ways to do this:

specify "parallel" for each underlying table. For more information on this, see .

place a comment in the SELECT statement that the view contains to give a hint of "parallel" to the Oracle optimizer. For more information on how to do this, see Oracle7 Server Tuning.

There is no special syntax required for partition views. Oracle interprets a UNION ALL view of several tables, each of which have local indexes on the same columns, as a partition view. To confirm that Oracle has correctly identified a partition view, examine the output of the EXPLAIN PLAN command. For more information on EXPLAIN PLAN, or on partition views, see Oracle7 Server Tuning.

Example I

The following statement creates a view of the EMP table named DEPT20. The view shows the employees in department 20 and their annual salary:

CREATE VIEW dept20

	AS SELECT ename, sal*12 annual_salary

		FROM emp

		WHERE deptno = 20

Note that the view declaration need not define a name for the column based on the expression SAL*12 because the subquery uses a column alias (ANNUAL_SALARY) for this expression.

Example II

The following statement creates an updatable view named CLERKS of all clerks in the employee table; only the employees' IDs, names, and department numbers are visible in this view and only these columns can be updated in rows identified as clerks:

CREATE VIEW clerk (id_number, person, department, position) 
	AS SELECT empno, ename, deptno, job 
		FROM emp 
		WHERE job = 'CLERK'
 	WITH CHECK OPTION CONSTRAINT wco

Example III

The following statement creates a read only view named CLERKS of all clerks in the employee table; only the employee's IDs, names, and department numbers are visible in this view:

CREATE VIEW clerk (id_number, person, department, position) 
	AS SELECT empno, ename, deptno, job 
		FROM emp 
		WHERE job = 'CLERK'
 	WITH READ ONLY

Because of the CHECK OPTION, you cannot subsequently insert a new row into CLERK if the new employee is not a clerk.