Sample Header Ad - 728x90

Refresh materialize View fast on commit multiple table

1 vote
2 answers
3907 views
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 .
Asked by Mohammad Mirzaeyan (117 rep)
Nov 8, 2017, 11:22 AM
Last activity: Sep 4, 2023, 01:02 PM