Oracle Views vs Tables- Advantages, Disadvantages and Features

  • Views are representation of simple or complex SQL statements
  • Views are created and stored in the database in the form of SQLs
  • View is a virtual table which provides the access to subset of columns from one or more base tables.
  • A view does not need any storage for the data as it is fetching the data from the base tables.
  • A view is a good method to present the data in particular users from accessing the table directly
  • View definition can be modified without affecting the base table data using create or replace view command
  • Views can provide additional level of table security by restricting the access to a set of rows or columns of a table
  • View hides the SQL coding complexity even though the view definition SQL statement has multiple joins or unions etc.
  • View can help you to populate some useful columns from the base table by using aggregate functions, operators, filters etc.
  • View isolates the application changes in the definitions of the base tables unless modifying the view base columns.
  • Create View system privilege is required to create a view in own schema
  • Create Any View system privilege is required to create a view in other schema.
  • USER_VIEWS is the data dictionary view to see the oracle base sql, owner etc.
  • CREATE FORCE VIEW command is used to create a view without the base table.
  • Views that are not updatable can be modified using an INSTEAD OF trigger
  • An UPDATABLE VIEW allows you to modify the base table. An updatable view must not contain GROUP BY, ORDER BY, CONNECT BY, START WITH, SUBQUERY, SET, DISTICT, and AGGREGATE FUNCIONS.
  • View increases the convenience in the coding by reusable codes

Advantages
  • Less parsing by reusable code: View is based on one SQL and there will not be any change in the View definition code. So when a view definition is called less parsing is required
  • Security: Views are used to hide the tables that actually contain data you are querying. Using views you can restrict the number of columns.  
Disadvantages
  • Oracle does not allow constraints on views
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google