In a materialized view with refresh fast on commit i have the below query
select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID,
pp.hierarchicode,pp.id, aup.userid
from app_useracess aup, core_organization pp;
when the app_useracess
changes the materialize view change the data with fast method but when a commit occure on core_organization
the materialize view does not refresh , unfortunately even i refresh it with this statement :
DBMS_SNAPSHOT.REFRESH( 'CORE_POWER_AUTHORIZE_ALLOW','F');
it does not refresh , and when i refresh it completely with C as a secound parameter it sense the data changes .
is it possible to refresh the materialize view with fast method and on commit on the core_organization
table ??
update : the MV DDL is :
create materialized view CORE_POWER_AUTHORIZE_ALLOW
refresh fast on commit
as
select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID,P.ROWID AS P_ROWID ,
pp.hierarchicode,pp.id, aup.userid,p.id as pID
from core_power p ,app_userspower aup, core_power pp
where p.id = aup.powerid
and pp.hierarchicode like p.hierarchicode || '%';
i also use dbms_mview.explain_mview
and the result is :
REFRESH_FAST_AFTER_INSERT Y REFRESH_FAST_AFTER_ONETAB_DML Y REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view
and there is so funny that the materialize refreshed base on this query :
select e.LAST_REFRESH_TYPE,e.LAST_REFRESH_DATE from dba_mviews e where e.mview_name = 'MV_NAME';
but the count(*) from my MV does not changes .