Create Materialized Views

Create table table for views:

CREATE TABLE ABC 
( FNAME VARCHAR2(20 BYTE) 
, LNAME VARCHAR2(20 BYTE) 
, ID NUMBER NOT NULL 
, UPD_TSTAMP TIMESTAMP(6) DEFAULT systimestamp )
TABLESPACE USERS;
CREATE UNIQUE INDEX ABC_PK ON ABC (ID ASC) 
LOGGING 
TABLESPACE USERS ;

ALTER TABLE ABC
ADD CONSTRAINT ABC_PK PRIMARY KEY 
( ID ) USING INDEX ABC_PK
ENABLE;

Insert some data into it table:

SET DEFINE OFF;
Insert into ABC (FNAME,LNAME,ID,UPD_TSTAMP) values ('ravi','reddy',2,to_timestamp('09-JAN-15 11.44.39.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into ABC (FNAME,LNAME,ID,UPD_TSTAMP) values ('arvind','reddy',3,to_timestamp('16-JAN-15 12.06.01.293176000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into ABC (FNAME,LNAME,ID,UPD_TSTAMP) values ('raghu','kumar',4,to_timestamp('20-JAN-15 04.33.35.616985000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into ABC (FNAME,LNAME,ID,UPD_TSTAMP) values ('ranii','boy',5,to_timestamp('20-JAN-15 04.47.08.157119000 PM','DD-MON-RR HH.MI.SSXFF AM'));

Now Lets create Materialized view:

create materialized view mv as select * from abc ;

select * from ABC ;

FNAME                LNAME                        ID UPD_TSTAMP                    
-------------------- -------------------- ---------- -------------------------------
ravi                 reddy                         2 09-JAN-15 11.44.39.000000000 AM 
arvind               reddy                         3 16-JAN-15 12.06.01.293176000 PM 
raghu                kumar                         4 20-JAN-15 04.33.35.616985000 PM 
ranii                boy                           5 20-JAN-15 04.47.08.157119000 PM 

update t set lname = upper(val) where id=3;
update t set lname = upper(val) where id=4;


Note how, after the update, the view data matches the table data but the materialized view data does not. Data in materialized views must be refreshed to keep it synchronized with its base table. 

Refreshing can either be done manually, as below, or automatically by Oracle in some cases.

execute dbms_mview.refresh( 'MV' );


Cleanup :

drop materialized view mv ;