Views

Views have two purposes:

• Simplify issuing complex SQL queries
• Restrict users' access to sensitive data.

Views are Database objects and are treated like tables. Unlike tables, views do not store any data. Views simply store a query.

View Type	Description
Simple View	A view based upon a sub query that only references one table and does not include any group function, expressions, or a GROUP BY clause.
Complex View	A view based upon a sub query that retrieves or derives data from one or more tables -- and may also contain functions or grouped data.
Inline View	A sub query used in the FROM clause of a SELECT statement to create a temporary table that can be referenced by the SELECT and WHERE clauses of the outer statement.
Materialized View	A view that replicates data by physically storing the results of the view query.

Creating a View

CREATE [ OR REPLACE ] [ FORCE | NO FORCE ] VIEW
viewname (colname, ...)
AS select statement
[ WITH CHECK OPTION [ CONSTRAINT constraintname ] ]
[ WITH READ ONLY ];

When a view is created it can not be modified. The OR REPLACE keywords tell Oracle that if there is a view that is already in place, then it is to be replaced. In the event that you are creating a view that uses a table that has not been created yet, then you can FORCE the creation of the view. The default mode is NO FORCE. The AS clause assigns a query to the view. The WITH CHECK OPTION prevents a user from submitting any DML commands. If the user were to change anything in the view then it would no longer be included in the view. To ensure that the user does not change any data that is included in a view the WITH READ ONLY option can be used.

Creating a Simple View

A simple view does not include any group functions, expressions or GROUP BY clauses. A simple view is just displaying table data.

CREATE VIEW inventory_vw
AS SELECT part#, part_name, retail, cost
FROM parts
WITH READ ONLY;

The above SQL will create a new view called inventory_vw. If there is already a view called inventory then an error will occur because we did not use the OR REPLACE keywords. If the WITH READ ONLY option was omitted, then the user could issue DML queries to alter the data that is in the view.

UPDATE inventory_vw
SET cost=44.99
WHERE part#=1230;

This would update the table so when the view was queried again the changes would be seen. If the view that was created only listed, say a certain part type and the user changed the part type, the table would be updated, but the view would no longer contain that item. The basic rule for DML operations on a simple view and/or a complex view is as long as the view was not created with the WITH READ ONLY option, any DML operation is allowed if it does not violate an existing constraint on the underlying base table. The user can add, modify and even delete data on the underlying table as long as the operation is not prevented by one of the following constraints:

• PRIMARY KEY
• NOT NULL
• UNIQUE
• FOREIGN KEY
• WITH CHECK OPTION

CREATE VIEW outstanding_orders_vw
AS SELECT customer, order#, orderdate, shipdate
FROM orders
WHERE shipdate IS NULL
WITH CHECK OPTION;

Doing any kind of updating that would remove any items from the view would not be allowed for that reason.

Complex Views

Creating a complex view is the same CREATE VIEW command as a simple view. The difference lies in the SELECT statement used to create the view. The SELECT statement that is contained in the view derives data from one or more tables and may contain functions or grouped data. Since complex views use more than one table and/or functions, certain DML operations are not permitted.

CREATE VIEW itemlist_vw
AS SELECT part#, part_name, cost, price, price-cost "profit" FROM products;

The above created a complex view because of the profit column. To update the view we can not issue the following DML query:

INSERT INTO itemlist_vw VALUES(2568, 'floppy drive', 5, 15, 10);

The profit column is a calculated field and therefore, no data should be directly inserted into that virtual column. The correct way is:

INSERT INTO itemlist_vw (part#, part_name, cost, price) VALUES(2568, 'floppy drive', 5, 15);

Another consideration is what about the constraints that are imposed on certain columns. If there are multiple tables included in the view, such as a join, inserting data into a join would not work. The only way to change the view would to be directly altering the underlying tables used in the view. Another thing to note is that when updating, only updates can be applied to one and only one table. The table that can be updated is the one that includes the primary key of an underlying table and is basically being used as the primary key for the view.

In the example above, if you excluded the part# column from the view, the part_name would become the primary key for the view. Since products is the only table we are using for the view, it becomes what Oracle calls a key-preserved table. a key-preserved table is a table that contains the primary key in the view.

If a view is created with a statement that contains a GROUP BY clause, then Oracle will not allow an individual record to be inserted.

A quick summary of complex views are:

• DML operation that violate a constrain are not permitted.
• A value cannot be added to a column that contains an arithmetic expression.
• DML operation are not permitted on non key-preserved tables.
• DML operation are not permitted on views that include group functions, a GROUP BY clause, the DISTINCT keyword, or the ROWNUM pseudo column.

Dropping a View

A view can be dropped or deleted by using the DROP VIEW command.

DROP VIEW viewname;

Inline View

Inline views create a temporary table that only exist during the execution of a command. This type of view is most often used when a person is using the "Top-N analysis" In the Top-N analysis, a table is created, and the first 5, 10, or x numbers are returned by using the ROWNUM pseudo column. This method is a work around for Oracle not allowing ORDER BY in the CREATE VIEW command.

SELECT colname, ....
FROM (subquery)
WHERE ROWNUM <= n;

the subquery should order the items, as this statement will return the first n rows that are put into the table.

Materialized Views

The difference between a View and a Materialized view is that data is actually stored in a materialized view rather than just a query. The advantages of using materialized views are:

• Segment the database, so queries can be performed on the materialized view rather than the tables itself.
• Remote users can perform queries on local databases rather than transferring data across the network.
• Data analysis may need data to be frozen for a specific period of time.

The disadvantages are:

• There is a need for storage space.
• Modifications need to be synchronized.
• If data in a materialized view needs to be updated constantly, then processing time may be minimal.

CREATE MATERIALIZED VIEW view_name
REFRESH COMPLETE
START WITH begin_time NEXT next_refresh
AS (query)

The REFRESH COMPLETE instructs Oracle to store a complete new copy of the view query results. The begin_time sets the point from where you want to start counting from while the next_refresh instructs Oracle when to refresh the data in the view. To drop a materialized view you MUST include the keyword MATERIALIZED in the DROP command.

DROP MATERIALIZED VIEW view_name;