Difference Between View and Materialised View

Step 1 : Create Base Table

create table T1(KEY number,VAL varchar2(10)); insert into t1 values(1,'a'); insert into t1 values(2,'b'); insert into t1 values(3,'c'); insert into t1 values(4,'');

Step 2 : Create Ordinary View

create view v as select * from t1 ;

Step 3 : Create Materialized View

create materialized view log on t1 with rowid; create materialized view mv refresh fast with rowid as select * from t1;

Step 4: Check for rowid similarity and difference in materialized view

select rowid from T1 order by rowid ; select rowid from v order by rowid ; select rowid from mv order by rowid ;

Step 5 := Update base table

update t1 set val = upper(val);

Step 6 := After DML try to select

select * from T1 order by rowid ; select * from v order by rowid ; select * from mv order by rowid ;

Step 7 :- Refersh your materialized View

execute dbms_mview.refresh( 'MV' );

Step 8 := Try to update Base table Via both the view

update v set val = lower(val); -- View will be create update mv set val = lower(val); -- Here it won't

Stpe 9 := Drop all objects.

drop materialized view mv ; drop view v ; drop table t1;