Materialized Views - Pro Oracle Database 12c Administration - page 420

After you create the table, run the EXPLAIN_MVIEW procedure to populate it:
SQL> exec dbms_mview.explain_mview(mv=>'SALES_MV',stmt_id=>'100');
Now, query MV_CAPABILITIES_TABLE to see what potential issues this MV may have:
select capability_name, possible, msgtxt, related_text
from mv_capabilities_table
where capability_name like 'REFRESH_FAST_AFTER%'
and statement_id = '100'
order by 1;
Next is a partial listing of the output. The P (for possible) column contains an N (for no) for every fast refresh
possibility:
CAPABILITY_NAME P MSGTXT RELATED_TEXT
------------------------------ - ----------------------------------- ---------------
REFRESH_FAST_AFTER_INSERT N the SELECT list does not have the B
rowids of all the detail tables
REFRESH_FAST_AFTER_INSERT N mv log must have ROWID MV_MAINT.REGION
REFRESH_FAST_AFTER_INSERT N mv log must have ROWID MV_MAINT.SALES
MSGTXT indicates the issues: The MV logs need to be ROWID based, and the ROWID of the tables must appear in the
SELECT clause. So, first drop and recreate the MV logs with ROWID (instead of a primary key):
drop materialized view log on region;
drop materialized view log on sales;
--
create materialized view log on region with rowid;
create materialized view log on sales with rowid;
--
drop materialized view sales_mv;
--
create materialized view sales_mv
as
select
a.rowid sales_rowid
,b.rowid region_rowid
,a.sales_id
,b.reg_desc
from sales a
,region b
where a.region_id = b.region_id;
Next, reset the MV_CAPABILITIES_TABLE , and repopulate it via the EXPLAIN_MVIEW procedure:
SQL> delete from mv_capabilities_table where statement_id=100;
SQL> exec dbms_mview.explain_mview(mv=>'SALES_MV',stmt_id=>'100');