Define view

View is Virtual table derived from one or more base tables.View is not stored in database so its do not occupy any space .It just logical representation of data. Whatever DML operations  performed on view it affect base tables.

       

Can we Create index on view?

       No

How To check view exits or not?
     

 select  * from user_views where view_name='View_Name';

Can we update complex view?if yes Then How to Update Complex View?
           

An instead of triggers transparent way of modifying a view that can not be modified directly trough DML statement such insert,update,delete.
An instead of trigger defined on view is fired when an insert ,update and delete statement executed against view.
When fired,the instead of triggers modify underlying base tables appropriately.
An instead of trigger fired on view is always row level trigger that is fired when for each row of view 

Example : 

Create view EmpDpt as
select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e,dept d
from emp e,dept d where e.deptno=d.deptno;

We have created complex view EmpDept.Now We will create trigger to insert values into  EmpDept view


Create Or replace trigger UpdateComplexView
instead of insert  on EmpDept
for each row 
begin

insert into Emp(empno,ename,esal)values (:NEW.empno,:NEW.ename,:NEW.esal); 

 insert into Dept(deptno,dname) values (:NEW.deptno,:NEW.dname):  
    
end;

Difference between View vs Materialized View in database

Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table

6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL

Mostly in application we use views because they are more feasible,  only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application.

What is Force View?

We can  create view without base tables these type of views known as force view. Force view is created using Keyword Force.Force View created with Compilation Error.

Syntax : 

  Create Or replace  Force View  test_View  

  As select * from employees;

If table dropped, then what happen view?

  View become invalid state