ORA-12032: cannot use rowid column from materialized view log on "DCS_BIZ"."TMP_12"

一、问题原因

 

SQL> CREATE MATERIALIZED VIEW MV_TMP_12
  2    refresh FAST on DEMAND
  3   -- with primary key
  4   START WITH sysdate NEXT sysdate+ 1/1440
  5   enable query rewrite
  6   AS
  7      select count(*),name from tmp_12
  8        group by name;
 
CREATE MATERIALIZED VIEW MV_TMP_12
  refresh FAST on DEMAND
 -- with primary key
 START WITH sysdate NEXT sysdate+ 1/1440
 enable query rewrite
 AS
    select count(*),name from tmp_12
      group by name
 
ORA-12032: cannot use rowid column from materialized view log on "DCS_BIZ"."TMP_12"
 

 

 

二、原因分析

 

CREATE MATERIALIZED VIEW LOG ON TMP_12
WITH PRIMARY KEY
INCLUDING NEW VALUES;

 

 

注：fast刷新方式不支持group by