Views in Oracle

A View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables. For example, if we frequently issue the following query

SELECT customerid, customername FROM customers WHERE countryid='US';

To create a view use the CREATE VIEW command as seen in this example

CREATE VIEW view_uscustomers

AS

SELECT customerid, customername FROM customers WHERE countryid='US';

This command creates a new view called view_uscustomers. Note that this command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view. This means that every time you query this view, Oracle has to go out and execute the view and query the database data. We can query the view like this:

SELECT * FROM view_uscustomers WHERE customerid BETWEEN 100 AND 200;

And Oracle will transform the query into this:

SELECT * 

FROM (select customerid, customername from customers WHERE countryid='US') 

WHERE customerid BETWEEN 100 AND 200

Prerequisites

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

To create a subview, you must have the UNDER ANY VIEW system privilege or the UNDER object privilege on the superview.

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. The owner must be granted these privileges directly, rather than through a role.

To use the basic constructor method of an object type when creating an object view, one of the following must be true:

• The object type must belong to the same schema as the view to be created.
• You must have the EXECUTE ANY TYPE system privileges.
• You must have the EXECUTE object privilege on that object type.
  
  

Semantics

1. OR REPLACE

Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.

INSTEAD OF triggers defined in the view are dropped when a view is re-created.

If any materialized views are dependent on view, then those materialized views will be marked UNUSABLE and will require a full refresh to restore them to a usable state. Invalid materialized views cannot be used by query rewrite and cannot be refreshed until they are recompiled.

FORCE

Specify FORCE if you want to create the view regardless of whether the base tables of the view or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.

If the view definition contains any constraints, CREATE VIEW ... FORCE will fail if the base table does not exist or the referenced object type does not exist.CREATE VIEW ... FORCE will also fail if the view definition names a constraint that does not exist.

NO FORCE

Specify NOFORCE if you want to create the view only if the base tables exist and the owner of the schema containing the view has privileges on them. This is the default.

Benefits of using Views:

Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing. This is because the basic underlying SQL that is called is always the same. However, since you can add additional where clauses when calling a view, you still need to use bind variables. Additional where clauses without a bind variable can still cause a hard parse!

Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Using views for security on less complex databases is probably not a bad thing. As databases become more complex, this solution becomes harder to scale and other solutions will be needed.

* Predicate pushing. Oracle supports pushing of predicates into a given view. Assume we had a set of layered views, like this:

-- View One

CREATE VIEW vw_layer_one

AS SELECT * FROM emp;

-- view two

CREATE VIEW vw_layer_two_dept_100

AS SELECT * FROM vw_layer_one

WHERE deptno=100;

Then assume we issued this query:

SELECT * FROM vw_layer_two_dept_100

WHERE empid=100;

The predicate in this statement is the where empid=100 statement. You may have one of tens or even hundreds of predicates (if you have hundreds, we don’t want to be supporting your SQL code!). Oracle will, in many cases, push those predicates down into the views being called. Thus, Oracle will transform the VW_LAYER_ONE view into a SQL statement that looks like this:

CREATE VIEW vw_layer_one

AS SELECT * FROM emp

WHERE deptno=100

AND empid=100;

Note that both the predicate from view two (where deptno=100) and the predicate from the SQL statement being executed (where empid=100) are pushed down into the final view that is executed. This can have significant performance benefits because now the bottom view can possibly use an index if one exists on deptno and/or empid.

Predicate pushing has a number of restrictions that are beyond the scope of this book, but you can find them in the Oracle documentation. Also, any predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure you use bind variables instead of literals in SQL code calling views. Thus, our SQL should look something like this instead for best performance:

SELECT * FROM vw_layer_two_dept_100

WHERE empid=:b100;

http://docs.oracle.com/cd/B28359_01/server.111/b28310/views001.htm