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;
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 :
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 .