Saturday, November 10, 2012

Views in Oracle

             View is a database object which contain logical representation of data. The main advantage of the view is we can hide some confidential information from the user.
We can create a view based on the entire table or based on the particular table.

  • If we apply the DML operations on view, the same operations automatically effected to corresponding base table and vice versa.
  • We can create a view based on another view like that we can create up to 32 views.
  • We can create a view with a base table (or) without a base table.
  • Once we drop the base table, the corresponding views become invalid. The views become invalid in three cases.
  1. When we drop the base table
  2. When we change the table name
  3. When we modify the structure of the base table
  •  On invalid views, we can’t perform any type of operations, we can make a invalid view as a valid view.
  • Views are classified into two types
  1. Simple Views
  2. Complex Views

Simple View:
Creating a view based on one table then those views can be called as simple views.
  • Syntax to create a simple view
           Syntax   : create view <view_name> as select * from <table_name>;
           Example : create view v as select * from emp;
  • Syntax to create a view without a base table
           Syntax   : create force view <view_name> as select * from <table_name>;
           Example : create force view v1 as select * from bbc;
  • Based on the partial table
           Syntax   : create view <view_name> as select col1,col2,…,coln from <table_name>;
           Example : create view v2 as select eid,ename from emp;

Read Only Views: This is the special type of view on which we can’t perform DML operations.
    Syntax   : create view 
<view_name> as select * from <table_name> with read only;
    Example : create view v3 as select * from emp with read only;

Complex View:
Creating a view based on more than one table then those views can be called as complex views.

Syntax   : create view 
<view_name> as select * from <table_name1>,<table_name2>,… where <condition>;
Example : create view v as select * from emp,dept where emp.deptno=dept.deptno;

--> The above example returns the following error message
       Duplicate column name

Example: create view v 
as select * from emp natural join dept;

NOTE: We can’t perform the DML operations on complex views some times update or delete are possible.

Syntax to see list of views:
      Syntax: select * from user_views;

Syntax to drop a view:
Syntax: drop view <view_name>;

NOTE: We can create a view based on the synonym and we can create a synonym based on the view.

Materialized Views:
                  This is a special type of view, the difference between normal view and materialized view is, in case of normal view it becomes invalid once we drop the base table but in case of materialized view even though we drop the base table the corresponding materialized view will not become invalid.
                   Before creating the materialized view the normal user need to take the permission from the DBA(Data Base Administrator).

NOTE:On which table we need to create the materialized view the table should contain primary key constraint.

Syntax to create a materialized view:
     Syntax   : create materialized view 
<view_name> as select * from <table_name>;
     Example :create materialized view mv as select * from emp;

NOTE: On materialized view we can’t perform any type of operations.

  • Materialized view and snapshot both are exactly same. The older version of oracle usually called as snapshot.

No comments:

Post a Comment

back to top