Oracle物化视图创建报错ORA-12052及ROWID使用疑问

问题场景 #

执行以下物化视图及日志创建语句时触发ORA-12052错误：

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;

错误信息：

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

ORA-12052错误原因及解决办法 #

ORA-12052表示无法对该物化视图执行FAST刷新，核心原因是当前配置不满足多表关联物化视图FAST刷新的要求：

1. 物化视图未包含基表ROWID：
   指定MV使用WITH ROWID模式刷新，但SELECT *不会自动包含基表的ROWID伪列。FAST刷新时需要通过ROWID匹配基表和MV中的行，必须在查询中显式引用两个表的ROWID。
2. 物化视图日志与MV刷新模式不匹配：
   虽然日志中指定了WITH ROWID，但MV的查询未暴露ROWID，导致刷新过程无法利用日志中的ROWID信息定位变更行。

修复方案 #

修改物化视图的SELECT语句，显式包含两个基表的ROWID：

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 AS E_ROWID, m.ROWID AS M_ROWID, e.*, m.* 
FROM EVENTS e, MANTEINANCE m
WHERE e.AIRCRAFT = m.AIRCRAFT AND 
e.MONTHS_YEARS  = m.MONTHS_YEARS;

如果基表EVENTS和MANTEINANCE的AIRCRAFT+MONTHS_YEARS是联合主键，也可以改用主键模式刷新：

1. 重新创建物化视图日志：

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

2. 修改物化视图定义为WITH PRIMARY KEY：

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

已有PK仍需指定ROWID的原因 #

物化视图FAST刷新支持两种行标识机制：ROWID模式和PRIMARY KEY模式，两者是独立的刷新逻辑：

• 当在MV中指定WITH ROWID时，Oracle会采用物理地址（ROWID）来跟踪基表和MV中的行对应关系，此时无论基表是否有PK，都需要物化视图日志包含ROWID，且MV查询中必须显式携带基表ROWID，否则刷新时无法定位变更行。
• 如果想利用基表PK来替代ROWID，需要将MV和日志切换到PRIMARY KEY模式：日志用WITH PRIMARY KEY创建，MV指定WITH PRIMARY KEY，此时Oracle会通过主键列匹配行，无需依赖ROWID。

简言之，ROWID和PK是两种并行的刷新标识方案，选择了ROWID模式，就必须满足该模式的要求，和基表是否有PK无关。

内容的提问来源于stack exchange，提问作者Norhther