[Oracle工程师手记]两表结合的MVIEW的高速刷新

对两表结合查询建立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>

 

可以看到，已经成功。