Oracle Views vs Tables- Advantages, Disadvantages and Features

advertisements

_____________________________________________________________________________________________________________________

• 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

_____________________________________________________________________________________________________________________

Website Stats