create table xxjk_master
as
select level id, date '2017-01-01' + mod(level,365) creation_date from dual connect by level <= 100000Table created.
alter table xxjk_master add constraint xxjk_master_pk primary key (id)Table altered.
create table xxjk_date
as
select sysdate - 100 refresh_date from dualTable created.
alter table xxjk_date add constraint xxjk_date primary key (refresh_date)Table altered.
create materialized view log on xxjk_master with rowidStatement processed.
create materialized view log on xxjk_date with rowidStatement processed.
create materialized view xxjk_tst_mv
refresh
on demand
fast
as
select i.rowid i_rid, d.rowid d_rid, i.id, i.creation_date from xxjk_master i, xxjk_date d
where i.creation_date > d.refresh_date Statement processed.
select count(*) from xxjk_tst_mv| COUNT(*) | 15609 |
|---|
insert into xxjk_master (id, creation_date) values (-1, sysdate)1 row(s) inserted.
insert into xxjk_master (id, creation_date) values (-2, sysdate-500)1 row(s) inserted.
select * from MLOG$_XXJK_MASTER| M_ROW$$ | SNAPTIME$$ | DMLTYPE$$ | OLD_NEW$$ | CHANGE_VECTOR$$ | XID$$ | AAgD+KAAXAAAA/zAAA | 01-JAN-00 | I | N | FE | 50665508692840391 | AAgD+KAAXAAAA/zAAB | 01-JAN-00 | I | N | FE | 50665508692840391 |
|---|
select * from MLOG$_XXJK_DATEno data found
exec dbms_mview.refresh(list=>'xxjk_tst_mv', method=>'F')Statement processed.
select count(*) from xxjk_tst_mv| COUNT(*) | 15610 |
|---|
update xxjk_date set refresh_date = date '2017-12-31'1 row(s) updated.
select * from MLOG$_XXJK_MASTERno data found
select * from MLOG$_XXJK_DATE| M_ROW$$ | SNAPTIME$$ | DMLTYPE$$ | OLD_NEW$$ | CHANGE_VECTOR$$ | XID$$ | AAgD+MAAXAAABBzAAA | 01-JAN-00 | U | U | 02 | 13229457049432500 |
|---|
exec dbms_mview.refresh(list=>'xxjk_tst_mv', method=>'F')Statement processed.
select count(*) from xxjk_tst_mv| COUNT(*) | 1 |
|---|
drop materialized view xxjk_tst_mvStatement processed.
drop table xxjk_masterTable dropped.
drop table xxjk_dateTable dropped.