Dear friends
oracle db 19.3
I have a fast refresh on commit materialised view such as :
create materialized view stock_total_mv
build immediate
refresh fast
on commit
enable query rewrite
as select sum(qty),sum(qty_reserved),count(1) count
from stock;
When I do quite a lot of inserts (including gather table stats after) of new stock records all is fine.
I can see the in :
select mview_name,staleness
from all_mviews m
where owner = <myowner>
and mview_name = 'STOCK'
this shows a status of 'FRESH
however after a number of updates to the underlying stock quantity (including gather table stats after) the above query shows a status of UNUSABLE.
the materialised view does however remain usable.
The only way I have found to clear this unusable status is to do a full refresh of the materialised view which seems to defeat the purpose of the refresh fast on commit.
I have tried gathering stats on the mview.
Am I missing something with this staleness of UNKNOWN