Refresh on commit for materialized view

0
votes

I have 2 base operational table test_table1 and test_table2 on which i have created the materialized view logs . I have created a view on top of this called test_view1 which is a join of test_table1 and test_table2. Now, I need to create a materialized view on this view test_view1.

I created the materialized view as REFRESH ON COMMIT and it got created but when i run the procedure for the incremental refresh as per

execute DBMS_MVIEW.REFRESH('test_mview2', 'F', '', TRUE, FALSE, 0,0,0,FALSE, FALSE);

the materialized view is not getting updated even if any DML was applied on the base tables. But, when i run the procedure for complete refresh:

execute DBMS_MVIEW.REFRESH('test_mview2', 'C', '', TRUE, FALSE, 0,0,0,FALSE, FALSE);

it is getting updated.

How can i REFRESH ON COMMIT for this particular materialized view ?

The output of dbms_mview.explain_mview: (CApability_name --possible--msgtxt)

  1. REFRESH_FAST_PCT --N-- PCT is not possible on any of the detail tables in the mater
  2. REWRITE_FULL_TEXT_MATCH --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details
  3. REWRITE_FULL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view
  4. REWRITE_PARTIAL_TEXT_MATCH --N-- materialized view cannot support any type of query rewrite
  5. REWRITE_PARTIAL_TEXT_MATCH --N-- query rewrite is disabled on the materialized view
  6. REWRITE_GENERAL --N-- materialized view cannot support any type of query rewrite
  7. REWRITE_GENERAL --N-- the reason why the capability is disabled has escaped analys
  8. REWRITE_GENERAL --N-- query rewrite is disabled on the materialized view
  9. REWRITE_PCT --N-- general rewrite is not possible or PCT is not possible on an
  10. PCT_TABLE_REWRITE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details
  11. PCT_TABLE_REWRITE --N-- relation is not a partitioned table
  12. PCT --N--
  13. REFRESH_COMPLETE --Y--
  14. REFRESH_FAST --N--
  15. REWRITE --N--
  16. PCT_TABLE --N-- Oracle error: see RELATED_NUM and RELATED_TEXT for details
  17. PCT_TABLE --N-- relation is not a partitioned table
  18. REFRESH_FAST_AFTER_INSERT --N-- mv references PL/SQL function that maintains state
  19. REFRESH_FAST_AFTER_ONETAB_DML --N-- see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

1
oraclematerialized-views
What result do you get from DBMS_MVIEW.EXPLAIN_MVIEW for this materialized view? – David Aldridge
Add the output of "SELECT capability_name, possible, SUBSTR(msgtxt,1,60) AS msgtxt FROM mv_capabilities_table" to the question. – David Aldridge
Add it to the question, for the love of gods! Stop pasting stuff into comments like a big unreadable pile of crap! – David Aldridge

1 Answers

0
votes

From the MV Capabilities result:

REFRESH_FAST_AFTER_INSERT N mv references PL/SQL function that maintains state

You have a PL/SQL function defined as part of the materialized view, or the view that it references, that is preventing fast refresh from working. My bet would be that it has a SELECT statement in it, but in any case it is not deterministic.