1

I created tables mails_outside, contacts_firm and contacts_outside. Now I want to make a materialized view based on them, so the user will have access to mails only related to himself. I want it to refresh on every commit, so the best would be fast refresh, but for some reason, I cannot make it, because "The materialized view did not satisfy conditions for refresh at commit time." Of course I created materialized view logs. Is there any way to make this view work?

materialized view

CREATE MATERIALIZED VIEW MV_US1_OUTSIDE
REFRESH FAST
ON COMMIT
AS
SELECT mo.rowid AS mo_rowid,
       cf.rowid AS cf_rowid,
       co.rowid AS co_rowid,
       mo.arrival_date,
       co.address,
       co.last_name || ' ' || co.first_name name,
       mo.message
  FROM mails_outside mo, contacts_firm cf, contacts_outside co
 WHERE mo.id_receiver = cf.id_contact
   AND mo.id_sender = co.id_contact
   AND cf.nick_name = user;

materialized view logs

CREATE MATERIALIZED VIEW LOG ON mails_outside
WITH rowid
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON contacts_firm
WITH rowid
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON contacts_outside
WITH rowid
INCLUDING NEW VALUES;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Saygon
  • 11
  • 1
  • 2
    I imagine it doesn't like the `USER` reference - [from docs](https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-505C24CF-5D56-4820-88AA-2221410950E7) "must not contain references to non-repeating expressions like SYSDATE and ROWNUM", and `USER` is similar. You may find [this](https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-651B08EB-4D32-4A93-A260-A965C40AE136) helpful. Does that filter condition really make sense anyway - what are you hoping it will do, show each user only their own data? – Alex Poole Jun 03 '21 at 09:18
  • 2
    @BarbarosÖzhan, that's a bug (however, Oracle considers it as "lack of documentation"). If you like to use `FAST` refresh, you have to use the old Oracle join syntax, see also https://stackoverflow.com/questions/43802118/is-it-good-to-always-use-join-clause-in-oracle/43802256#43802256 – Wernfried Domscheit Jun 03 '21 at 09:38
  • nice to learn that, thank you @WernfriedDomscheit – Barbaros Özhan Jun 03 '21 at 09:42
  • My error was "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized". I want the materialized view to be something inbox-alike, so it will show messages only related to user. I think that you are right Alex, I can make separate views for users without ```user``` reference, now it worked. – Saygon Jun 03 '21 at 09:42
  • Thank you all for fast response! Good to know, that sometimes it is better to use join syntax. – Saygon Jun 03 '21 at 09:45

0 Answers0