Views

View :  To restrict the columns access within a schema  we use view.

Types of view :

1. Simple view
2. Complex view
3. Materialized view

Simple view :

• View created based on Single table  is called simple view.
• We can perform DML operations on view.
• Changes made to view data will be reflected on original table.
• view doesn't store data.

Example :

SQL>Create view employee_v as select name,no,dno from employee;

Note :

• create view in one schema .
• grant privileges on that view to another schema user.

SQL>Grant select on employee_v to <schema user name>

Complex view : 

• When multiple tables participates in creation of view it is called complex view .
• Joins are used.
• DML operations can't be performed.

Example:
SQL> create view employee_v as (select a.name,a.no,a.dno,b.dname from employee a,department b where a.dno=b.dno);
Note : view is an object which has to be access from one schema to another . So again grant privileges are required.

Materialized view :

• Is a database object
• Advantages over normal and complex views.
• Always used in real time for reporting.

Example :

SQL>materialized view employee_mv complete refresh based on primary key start with sysdate next sysdate +1 as select name,no from emp;
Difference between view and materialized view:
View :

• Has logical existence.
• Changes reflected to original table.
• Cant perform DML operations on complex view
• As view always called then it calls the sub query which takes response time.

Materialized view.

• Has physical existence.
• Changes not reflected to original table.
• Can perform DML operations on it.
• Can perform auto refreshing on materialized view. on  schedule basis.
• performance is better.