Create a new materialized view without a cleaned materialized view log

Question

I would like to use the materialized view in the finished table to synchronize the table before the migration. Data is constantly changing, so you need to track changes between the start of export and import. It goes without saying that the table is huge, so a full update is too slow.

ID of the steps to be performed:

• Create table in new db.
• Create mv log in old db table.
• Import data from old db to new db.
• Create a materialized view in the new db of the prebuild table and save it from the moment the mv log was created.

The problem is that when the materialized view is created, the mv log on the old table is cleared.

Old DB: create a table kvrtest (identifier number, cat number);

alter table kvrtest add ( constraint pkkvrtest primary key (id) using index); insert into kvrtest (id, cat) values (1, 1); commit; CREATE MATERIALIZED VIEW LOG ON kvrtest WITH PRIMARY KEY; insert into kvrtest (id, cat) values (2, 1); insert into kvrtest (id, cat) values (3, 2); commit; select * from MLOG$_KVRTEST; --Yields 2, these should be caught by a fast refresh. 

New DB: create the kvrtest table (identifier number, cat number);

 alter table kvrtest add ( constraint pkkvrtest primary key (id) using index); insert into kvrtest (id, cat) values (1, 1); --Simulate import. commit; CREATE MATERIALIZED VIEW kvrtest ON PREBUILT TABLE WITHOUT REDUCED PRECISION USING INDEX REFRESH FORCE ON DEMAND AS select * from kvrtest@oldDb ; 

At this point, mv log is cleared

 select * from MLOG$_KVRTEST; --Yields 0, a fast refresh from here does not catch these records. 

Any suggestions?

Answer 1

when you create a new MATERIALIZED VIEW with the REFRESH tag log table, it is cleared because our view is valid after creation.

 CREATE MATERIALIZED VIEW kvrtest ON PREBUILT TABLE WITHOUT REDUCED PRECISION USING INDEX REFRESH FORCE ON DEMAND AS 

if you do not want to clear the log table, use NEVER REFRESH, and then change the REFRESH ON DEMAND value as follows:

 CREATE MATERIALIZED VIEW kvrtest ON PREBUILT TABLE WITHOUT REDUCED PRECISION USING INDEX NEVER REFRESH AS select * from kvrtest@oldDb ; 

see our log table

 select * from MLOG$_KVRTEST; 

We have two lines, alright! and then

 ALTER MATERIALIZED VIEW kvrtest REFRESH ON DEMAND; 

then after updating the log table will be cleared again.