Sunday, May 8, 2011

Difference Between View and Materialized 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;


Prepared By
Sivakurunath S
Post a Comment