Difference Between View and Materialized View with Examples

Difference Between View and Materialized

1. Materialized views are disk based and update periodically base upon the query definition.


2. Views are virtual only and run the query definition each time they are accessed.

Difference Between View and Materialized Working Example

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 :- Refresh 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

Step 9 := Drop all objects.

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