Error creating a materialized view in Oracle-CodePudding

I'm trying to build the following MV:

CREATE MATERIALIZED VIEW LOG ON EVENTS
   WITH ROWID, SEQUENCE(AIRCRAFT, MONTHS_YEARS) INCLUDING NEW VALUES;
  
 CREATE MATERIALIZED VIEW LOG ON  MANTEINANCE
   WITH ROWID, SEQUENCE(AIRCRAFT, MONTHS_YEARS) INCLUDING NEW VALUES;
  

CREATE MATERIALIZED VIEW MV_LOGBOOK
BUILD IMMEDIATE 
REFRESH FAST START WITH (SYSDATE) NEXT (add_months(trunc(sysdate,'mm'),1)) WITH ROWID
ON DEMAND
DISABLE QUERY REWRITE AS
SELECT * FROM EVENTS e, MANTEINANCE m
WHERE e.AIRCRAFT = m.AIRCRAFT AND 
e.MONTHS_YEARS  = m.MONTHS_YEARS;

I created the logs associated with each table, also including the values inside the where clause, however, it gives me the error:

SQL Error [12052] [99999]: ORA-12052: no se puede realizar un refrescamiento rápido de la vista materializada MV_LOGBOOK

And I'm unsure why this is happening. Also, I would like to ask why I need a ROWID if there is already a PK. Thanks.

CodePudding user response：

An MV with joins must explicitly include the ROWID from each source table in the MV columns. You should also explicitly name every column and not use implicit selects, and use column aliases to differentiate columns with the same name because just like any table or view you can't have multiple columns with the same name:

CREATE MATERIALIZED VIEW MV_LOGBOOK
BUILD IMMEDIATE 
REFRESH FAST START WITH (SYSDATE) NEXT (add_months(trunc(sysdate,'mm'),1)) WITH ROWID
ON DEMAND
DISABLE QUERY REWRITE AS
SELECT e.rowid e_rowid, 
    m.rowid m_rowid, 
    e.col1 e_col1, 
    e.col2 e_col2, 
    m.col1 m_col1, 
    m.col2 m_col2
FROM EVENTS e, MANTEINANCE m
WHERE e.AIRCRAFT = m.AIRCRAFT AND 
e.MONTHS_YEARS  = m.MONTHS_YEARS;