Wednesday, March 31, 2010

What are the advantages of view over table in oracle

What is the differences between table, view , synonym and alias in Oracle.

What are advantages and limitations of one over the other.


Table :

Relational Database is composed of tables that contain related data.
Storage Unit Contain Rows and Columns.

View :

A view is a virtual table. Every view has a query attached to it.
(The query is a SELECT statement that identifies the columns
and rows of the table(s) the view uses.)

advantages of views

1. Views are created from one or more than one table by joins with selected columns.
Hide data complexity. Simplify commands for the user and Store complex queries.

2. Views acts as a layer between user and table.

3. Provide an additional level of table security, by restricting access
to a predetermined set of rows and columns of a table.
(that is not done in tables)

4. Views reduces the effort for writing queries to access specific columns every time.

5. Reports can be created on views.

6. View doesn't contain any data.

7. Modification through a view (e.g. insert update delete) generally
not permitted (complex view).

8. Logical Subset of Tables

9. A VIEW is only a mirror image of table which is used at places where
large access to a table is required.

10.Present the data in a different perspective from that of the base table.

A synonym is an alias for a table, view, sequence or program unit.
Provide location transparency for tables,views ,sequencies
Provide public access to an object

There are two types of synonyms private and public.
private synonyms

Only its owner can access a private synonym.

public synonym?
Any database user can access a public synonym.
synonyms used
- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.

it just like nick name
we can used it in quries when two tables have same column name
and also in big quries we can use alias name
for easy understand and readability.
always use alias name

No comments: