- 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 up to 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.
- When we drop the base table.
- When we change the table name.
- 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.
Creating the view based only one table is known as simple view.
Creating the view based on more than one table is called Complex view.
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>
create view bn as select * from lak;
Create a view base on Partial table
Create View <View name> a s select col1, col2 , -------- ,col n from <Table Name>
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.
create view kpp as select *from emp with read only;
Syntax to create a view without a base table
create force view <View Name> as select * from <Table name>
create force view fv as select * from aaa;
Warning: View created with compilation errors.
If we create a synonym for one table then we can’t create view for it and also same thing applicable for reverse.
Creating the views based on more than one table is known as complex views.
CREATE VIEWE<View Name> AS SELECT * FROM <tab1>,<tab2>-----,<tab n>
create view cv as select * from emp,dept where emp.deptno=dept.deptno;
- 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.
- On complex views we can’t perform any type of DML operations.
create view cv as select * from emp natural join dept;
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.
- He need to take permission from DBA.
- On which table we need to create the materialized view that table should contain primary key Constraint.
- Materialized view stores data.
- We can’t perform DML Operations on Materialized view.
create materialized view dv as select * from emp;