-
A view is like a virtual table. It takes the output of a query and treats it like a table.
-
A view can be based on one or more tables or other views. These tables/views are called base tables.
-
A view takes up no storage space other than for the definition of the view in the data dictionary.
-
A view contains no data. All the data it shows comes from the base tables.
-
A view can provide an additional level of table security by restricting access to a set of rows or columns of a table.
-
A view hides implementation complexity. The user can select from the view with a simple SQL, unaware that the view is based internally on a join between multiple tables.
-
A view lets you change the data you can access, applying operators, aggregation functions, filters etc. on the base table.
-
A view isolates applications from changes in definitions of base tables. Suppose a view uses two columns of a base table, it makes no difference to the view if other columns are added, modified or removed from the base table.
-
Using views encourages the use of shared SQL, which improves efficiency of frequently invoked SQL.
-
An updatable view allows you to insert, update, and delete rows by propagating the changes to the base table. A view can be updatable provided its definition does not contain any of the following constructs: SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery or collection expression in a SELECT list.
-
The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS,and USER_UPDATABLE_COLUMNS indicate which view columns are updatable.
-
Views that are not updatable can be modified using an INSTEAD OF trigger.
-
A view can be created even if the defining query of the view cannot be executed, using the CREATE FORCE VIEW command. Such a view is called a view with errors. This option can be useful for import/installation tools to create a view before the underlying objects are present.
-
A view can be replaced with a CREATE OR REPLACE VIEW statement. The REPLACE option updates the current view definition but preserves the present security authorizations.
-
A view lets you reorder columns easily with a CREATE OR REPLACE, rather than going into a messy drop column for the base table with data.
-
To know about the views in your own schema, look up user_views.
-
The underlying SQL definition of the view can be read via select text from user_views for the view.
-
Oracle does not enforce constraints on views. Instead, views are subject to the constraints of their base tables.
-
Be careful when you define views of views. Don’t do it just because it is easy to code – it may not be the optimal query. Check if you would be better off using the base tables directly instead.
-
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.
20 Things You Should Know About Oracle Views
Previous post: How to Reset a Sequence in Oracle
Next post: The Difference Between Views and Materialized Views
{ 4 comments… read them below or add one }
Very nice blog!
#19: “Check if you would be better off using the base tables directly instead.”
Can you clarify what this means. Check how?
@Aaron: A simplified example:
If view v1 =
(select empno, empname, sal
from emp where dept = 11),
and
view v2 =
(select empno
from v1 where sal < 10000), then a query like select * from v2; is internally doing: select * from ( select empno from ( select empno, empname, sal from emp where dept = 11 ) where sal < 10000 ) ) All you needed instead was: select empno from emp where dept = 11 and sal < 10000 The nesting of views isn't required and might have a performance impact. Hope this helps.
can i persist data permanently on view, is there any method to persist data on views. Interview Question.
@mahesh: See #4.