Recent Posts

Saturday, 9 July 2016

Views Tutorial


* View is a database object which contains logical representation of data.

* By using views we can hide some column data from the user.

* View does not occupy the physical memory.

* We can create a view based on entire table or partial table or subset of table.

* If we perform any type of operations view the same operations automatically affected to the corresponding base table and vice – versa.

* We can create a view based on another view upto maximum 32 views.

* If we drop the base table the corresponding views will not be dropped and those become invalid.

* On invalid views we can’t perform any type of operations.

* Views will 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 (Rename) of the base table.

* We can make an invalid view as a valid view.

* We can create a view based on one table and also based on the more than one table.

* Views are divided into two types.

Simple View
     Creating the view based only one table is known as simple view.
Complex View
     Creating the view based on more than one table is called Complex view.
Note
     We can create a view based on the synonym and also we can create a synonym based on the view.
Syntax to create view
CREATE VIEW <View Name> as SELECT * FROM <Table Name>
E.g
create view bn as select * from lak;

Create a view base on Partial table
Syntax
Create View <View name> a s select col1, col2 ,  -------- ,col n  from <Table Name>
E.g
create view see as select ename, sal from emp1;

Read Only View
     It is specific type of the view. So we can’t perform any type of operations of DML operations.
E.g
create view kpp as select *from emp with read only;

Syntax to create a view without a base table
Syntax
create force view <View Name> as select * from <Table name>
E.g
create force view fv as select * from aaa;
Warning: View created with compilation errors.
Note
     If we create a synonym for one table then we can’t create view for it and also same thing applicable for reverse.

Complex Views
     Creating the views based on more than one table is known as complex views.
Syntax
CREATE VIEWE<View Name> AS SELECT * FROM <tab1>,<tab2>-----,<tab n>
E.g
create view cv as select * from emp,dept where emp.deptno=dept.deptno;
Note
     Here we will get on error message. According to RDBMS rules no database object should not contain duplicate columns.
The above example returns the following error message.
Note
     On complex views we can’t perform any type of DML operations.
E.g
create view cv as select * from emp natural join dept;

Materialized View
     These are special type of views the main difference between normal view and materialized view is if we drop the base table it won’t invalid. Before creating the materialized view user need to remember two points.
1. He need to take permission from DBA.

2. On which table we need to create the materialized view that table should contain primary key Constraint.

3. Materialized view stores data.

4. We can’t perform DML Operations on Materialized view.
E.g
create materialized view dv as select * from emp;

Next Tutorial  Indexes Tutorial

Previous Tutorial Synonym Tutorial

No comments:

Post a Comment