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;
Prepared By
Sivakurunath S
