Refresh materialize View fast on commit multiple table

Question

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 .

• atokpas: Provide the complete DDL of the MV.
• Mohammad Mirzaeyan: i add the complete DDL of MV.

Answer 1

Just use DBMS_MVIEW.EXPLAIN_MVIEW + MV_CAPABILITIES_TABLE to find why fast refresh is not possible on your mview.

Fast refresh is possible without a join condition.

Create the table for storing explain results:

SQL> @?/rdbms/admin/utlxmv
Table created.

Create tables + mview:

create table t1 (c1 number primary key, c2 varchar2(20 char));
create table t2 (c3 number primary key, c4 varchar2(20 char));
create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;
create materialized view mv1
refresh fast on commit
as select t1.c1, t1.c2, t2.c3, t2.c4,
t1.rowid as rt1, t2.rowid as rt2 from t1, t2;

Explain mview and see fast refresh capabilities:

SQL> exec dbms_mview.explain_mview('MV1');
PL/SQL procedure successfully completed.
SQL> select capability_name, possible, msgtxt from mv_capabilities_table 
 where capability_name like 'REFRESH_FAST%'
CAPABILITY_NAME P MSGTXT
---------------------------------------- - ----------------------------------------
REFRESH_FAST Y
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

PCT fast refresh is not possible because there are no partitions in this example.

Verify fast refresh:

SQL> select * from mv1;
no rows selected
SQL> insert into t1 select rownum, 'A' from dual connect by level <= 2;
2 rows created.
SQL> insert into t2 select rownum, 'A' from dual connect by level <= 2;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from mv1;
 C1 C2 C3 C4 RT1 RT2
---------- -------------------- ---------- -------------------- ------------------ ------------------
 1 A 1 A AAAOOLAAEAAAACPAAA AAAOONAAEAAAACvAAA
 1 A 2 A AAAOOLAAEAAAACPAAA AAAOONAAEAAAACvAAB
 2 A 1 A AAAOOLAAEAAAACPAAB AAAOONAAEAAAACvAAA
 2 A 2 A AAAOOLAAEAAAACPAAB AAAOONAAEAAAACvAAB
SQL> select last_refresh_type from dba_mviews where mview_name = 'MV1';
LAST_REF
--------
FAST

• Mohammad Mirzaeyan: i use the DBMS_MVIEW.EXPLAIN_MVIEW + MV_CAPABILITIES_TABLE and add the result into my question

Answer 2

Fast refresh on commit is not working as expected when we create a nested materialized view on the base tables.

Nested MV is created based on a Join condition,Joining multiple nested tables.

Fast refresh status is completed but data is not matching.

It is a similar issue , but we have created mv based on mvs created locally.

From this it looks like it has a limitation on nested mvs as well.