0

I've created a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c):

create materialized view primary_system.workorder_mv
build immediate 
refresh force 
on demand 
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder_vw@gistomax --view in external system that is served up by dblink (gistomax)
    --the view looks like this: select * from workorder where isgis=1


create materialized view log on external_system.workorder with rowid;  

The docs say materialized views are supposed to purge automatically:

Oracle automatically tracks which rows in a materialized view log have been used during the refreshes of materialized views, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple materialized views can use the same materialized view log, rows already used to refresh one materialized view might still be needed to refresh another materialized view. Oracle does not delete rows from the log until all materialized views have used them.


Question:

So far, the MV log data doesn't seem to be purging automatically:

enter image description here

Why aren't' the MV logs automatically purging once the records are synced?

2
  • Do you only see the one materialized view in dba_registered_mviews? Nov 30, 2020 at 16:36
  • According to that screenshot, can_use_log is NO which means that the materialized view can't use the materialized view log. Since nothing is using the materialized view log, the log won't be purged-- it's assuming that you're going to be creating a materialized view that does use the log. Nov 30, 2020 at 17:18

1 Answer 1

1

Try basing your MV directly on the source table and not on an intermediate view. Include your "isgis=1" condition in the MV query.

create materialized view primary_system.workorder_mv
build immediate 
refresh force 
on demand 
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder@gistomax
    where isgis=1
4
  • 1
    The source DB needs to recognize that a query has been performed on the source table. Insulating it with an intermediate view over a db link prevents that. Assuming that works, you will also want to make sure you release any space accumulated in the MV Log while rows were not being purged, using "enable row movement" and "shrink space" on the MV Log table. Otherwise the MV will have to scan all blocks assigned to the MV Log when checking for updates, whether they contain rows or not. This can significantly affect performance, size of backups, etc.
    – pmdba
    Nov 30, 2020 at 18:29
  • 1
    You may also want to confirm - especially if you have dropped any remote MVs while testing - that you don't have orphaned MV Log registrations in the master database, as described here: dba-oracle.com/t_materialized_view_fast_refresh_performance.htm
    – pmdba
    Nov 30, 2020 at 18:39
  • It likely won't be able to use the log at all as long as you're going through the view. If it was using the log correctly, it would purge. As it is you will be doing full refreshes every time, which will put unnecessary load on the system and the network and make the MV Log useless. Purging the log manually won't work because you have no idea which records should be purged to sync correctly with your MV (especially if you ever have more than one MV using the table), not that you'd actually be using the log anyway.
    – pmdba
    Nov 30, 2020 at 20:41
  • 1
    From a performance standpoint, you want refreshes against the base table directly, as often as possible. This will keep the number of updates per refresh low and the size of the MV Log as small as possible. Smaller transactions = less load = better for everyone.
    – pmdba
    Nov 30, 2020 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.