Tag Archives: Materialized View

Materialized View with refresh fast Example

At first create a Table as basis for the Materialized view:


create table matviewtab as
with resNmb(iNmb) as
(select 1 as iNmb from dual
union all
select iNmb + 1 from resNmb where iNmb < 1005 ) select iNmb,'SomeText' as num_ber from resNmb;

Add a primary Key.

alter table matviewtab add constraint matviewtab_pk primary key (iNmb);

And create a Materialized View Log for capturing the operations on the basis-table.

CREATE MATERIALIZED VIEW LOG ON matviewtab WITH PRIMARY KEY, ROWID;

Then lets create the Materialized View with fast refresh option on commit:

CREATE MATERIALIZED VIEW matviewview
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * from matviewtab;

Check the content:

select * from matviewview order by inmb desc;

INMB NUM_BER
1005 SomeText
1004 SomeText
1003 SomeText
1002 SomeText
1001 SomeText
1000 SomeText
999 SomeText
998 SomeText
997 SomeText
996 SomeText

Insert a row and commit and check the content again:

insert into matviewtab values(1006,'OtherTxt');
commit;


select * from (select * from matviewview order by inmb desc) where rownum < 11 order by inmb desc;

INMB NUM_BER
1006 OtherTxt
1005 SomeText
1004 SomeText
1003 SomeText
1002 SomeText
1001 SomeText
1000 SomeText
999 SomeText
998 SomeText
997 SomeText

Insert another Row and check for changes in the DB-Objects user_tab_modifications and MLOG$_matviewtab (MLOG$_<>):

insert into matviewtab values(1007,'OtherTxt');


select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
BIN$TPmrAsfFAN7gU6wZ4BSJ9w==$0     2 0 0 12.04.2017 16:00:54 NO 0


select * from MLOG$_matviewtab;

INMB M_ROW$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$
1007 AAFaYQAQAAAAAA9AAB 01.01.4000 00:00:00 I N FE 3096379362658352


commit;


select * from (select * from matviewview order by inmb desc) where rownum < 11 order by inmb desc;

INMB NUM_BER
1007 OtherTxt
1006 OtherTxt
1005 SomeText
1004 SomeText
1003 SomeText
1002 SomeText
1001 SomeText
1000 SomeText
999 SomeText
998 SomeText


drop MATERIALIZED VIEW LOG on matviewtab;
drop MATERIALIZED VIEW matviewview;
drop table matviewtab;

Additional Infos:
Materialized Views: how can I find the number of updates, inserts, and deletes applied during refresh?