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?