对两表结合查询建立MVIEW，进行MVIEW的的高速刷新失败，如何处理？

例如：

SQL> drop user u1 cascade; User dropped. SQL> grant dba to u1 identified by u1; Grant succeeded. SQL> conn u1/u1 Connected. SQL> create table TAB001 (col1 integer primary key, col2 integer, val3 integer); Table created. SQL> create materialized view log on tab001; Materialized view log created. SQL> create table TAB002 (col1 integer primary key, col2 integer, val3 integer); Table created. SQL> SQL> create materialized view log on tab002; SQL> create materialized view mv001 as SELECT t1.col1 as t1c0l1, t1.col2 as t1col2, t2.col1 as t2col1, t2.col2 as t2col2 , t1.val3 as t1val3,t2.val3 as t2val3 FROM TAB001 t1 LEFT OUTER JOIN TAB001 t2 ON t1.col2 = t2.col2; Materialized view created. SQL> exec dbms_mview.refresh('MV001','F'); BEGIN dbms_mview.refresh('MV001','F'); END; * ERROR at line 1: ORA-12004: REFRESH FAST cannot be used for materialized view "U1"."MV001" ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994 ORA-06512: at line 1 SQL>

 

此时，可以考虑换用 ROWID 类型的 MVIEW LOG，重新来执行一次：

create table TAB001 (col1 integer primary key, col2 integer, val3 integer); alter table tab001 add constraint con_t1_col2 unique(col2); CREATE MATERIALIZED VIEW LOG ON tab001 WITH ROWID; create table TAB002 (col1 integer primary key, col2 integer, val3 integer); CREATE MATERIALIZED VIEW LOG ON tab002 WITH ROWID; CREATE MATERIALIZED VIEW MV055  BUILD IMMEDIATE  REFRESH FAST  AS select  /*+ use_hash(a,b) */     a.rowid aid, b.rowid bid, a.col1, b.val3     from tab001 a, tab002 b     where a.col2 = b.col2(+); exec dbms_mview.refresh('MV055','F'); SQL> exec dbms_mview.refresh('MV055','F'); PL/SQL procedure successfully completed. SQL>

 

可以看到，已经成功。