20 Things You Should Know About Oracle Views

November 22, 2010

in Data Dictionary, DBA, DDL, Views

Oracle Views
  1. A view is like a virtual table. It takes the output of a query and treats it like a table.

  2. A view can be based on one or more tables or other views. These tables/views are called base tables.

  3. A view takes up no storage space other than for the definition of the view in the data dictionary.

  4. A view contains no data. All the data it shows comes from the base tables.

  5. A view can provide an additional level of table security by restricting access to a set of rows or columns of a table.

  6. 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.

  7. A view lets you change the data you can access, applying operators, aggregation functions, filters etc. on the base table.

  8. 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.

  9. Using views encourages the use of shared SQL, which improves efficiency of frequently invoked SQL.

  10. 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.

  11. The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS,and USER_UPDATABLE_COLUMNS indicate which view columns are updatable.

  12. Views that are not updatable can be modified using an INSTEAD OF trigger.

  13. 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.

  14. 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.

  15. 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.

  16. To know about the views in your own schema, look up user_views.

  17. The underlying SQL definition of the view can be read via select text from user_views for the view.

  18. Oracle does not enforce constraints on views. Instead, views are subject to the constraints of their base tables.

  19. 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.

  20. 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.

{ 4 comments… read them below or add one }

1 Aaron November 23, 2010 at 11:40 pm

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?

2 oratabler March 3, 2012 at 11:30 am

@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.

3 mahesh August 3, 2017 at 12:19 pm

can i persist data permanently on view, is there any method to persist data on views. Interview Question.

4 oratabler August 4, 2017 at 11:16 pm

@mahesh: See #4.

Leave a Comment

Previous post:

Next post: