I have a table BEFUND. PK is column BEFUND_UBEID. A FK column is UBERGEBNIS_UBEID.
Now I want to create a MV on that table that always shows the latest (highest) BEFUND_UBEID for a given UBERGEBNIS_UBEID.
create materialized view log on BEFUND tablespace users1 with rowid ( befund_ubeid, ubergebnis_ubeid ) including new values;
Then I do:
create materialized view TOTAL_REPORT
refresh fast on commit
max( befund_ubeid ) LAST_BEFUND_UBEID,
count( befund_ubeid ) BEFUND_COUNT
group by ubergebnis_ubeid );
Info in user_mview states that FAST_REFRESHABLE is "DIRLOAD_LIMITEDDDML".
Now when my application creates a new row in BEFUND for a given UBERGEBNIS_UBEID with a new sequence-generated BEFUND_UBEID I would like the MV to fast refresh. It doesn't. STALENESS says it's UNUSABLE. I cannot trigger a manual fast refresh (ORA-12057).
What can I do to solve this?