创建物化视图错误ORA-12052的解决方法

用下面的命令创建materialized view遇到ora-12052错误：
create materialized view log on dept;
create materialized view log on emp ;
SQL> create materialized view mtrlview_test
  2  build immediate
  3  refresh fast on commit
  4  enable query rewrite
  5  as
  6  select d.dname, d.loc , e.ename, e.job, e.mgr, e.hiredate ,e.sal
  7  from dept d ,emp e
  8  where d.deptno=e.deptno;
from dept d ,emp e
     *
ERROR at line 7:
ORA-12052: cannot fast refresh materialized view myscm.MTRLVIEW_TEST
 
根据官方文档，错误的原因如下：
If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change.
These additional checks are:
A materialized view log must be present for each detail table unless the table supports PCT. Also, when a materialized view log is required, the ROWID column must be present in each materialized view log.
The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.
If the materialized view contains only joins, the ROWID columns for each table (and each instance of a table that occurs multiple times in the FROM list) must be present in the SELECT list of the materialized view.
以上的说明可以在
Oracle® Database Data Warehousing Guide10g Release 2 (10.2)Part Number B14223-02   第8章找到
并且这里还可以找到fast refresh针对特定情况的其它特殊要求
按照上面错误的做法：
create MATERIALIZED VIEW LOG ON emp with primary key;
create MATERIALIZED VIEW LOG ON dept with primary key;
正确的做法：
alter MATERIALIZED VIEW LOG ON emp add rowid;
alter MATERIALIZED VIEW LOG ON dept add rowid;
再次执行依然错误：
SQL> create materialized view mtrlview_test
  2  build immediate
  3  refresh fast on commit
  4  enable query rewrite
  5  as
  6  select d.dname, d.loc , e.ename, e.job, e.mgr, e.hiredate ,e.sal
  7  from dept d ,emp e
  8  where d.deptno=e.deptno;
from dept d ,emp e
     *
ERROR at line 7:
ORA-12052: cannot fast refresh materialized view ZOUBF.MTRLVIEW_TEST
原因是Select项目也要包括各个表的rowid
SQL> create materialized view mtrlview_test
  2  build immediate
  3  refresh fast on commit
  4  enable query rewrite
  5  as
  6  select d.rowid drowid , e.rowid erowid ,d.dname, d.loc , e.ename, e.job, e.mgr, e.hiredate ,e.sal
  7  from dept d ,emp e
  8  where d.deptno=e.deptno;

Materialized view created.
终于成功

测试查询重写query rewrite:
SQL> set autotrace on
SQL> select d.dname, d.loc , e.ename, e.job, e.mgr, e.hiredate ,e.sal
  2  from deptd ,emp e
  3  where d.deptno=e.deptno;


DNAME          LOC           ENAME      JOB              MGR HIREDATE         SAL
-------------- ------------- ---------- --------- ---------- --------- ----------
RESEARCH       DALLAS        SMITH      CLERK           7902 17-DEC-80        800
SALES          CHICAGO       ALLEN      SALESMAN        7698 20-FEB-81       1600
SALES          CHICAGO       WARD       SALESMAN        7698 22-FEB-81       1250
RESEARCH       DALLAS        JONES      MANAGER         7839 02-APR-81       2975
SALES          CHICAGO       MARTIN     SALESMAN        7698 28-SEP-81       1250
SALES          CHICAGO       BLAKE      MANAGER         7839 01-MAY-81       2850
ACCOUNTING     NEW YORK      CLARK      MANAGER         7839 09-JUN-81       2450
RESEARCH       DALLAS        SCOTT      ANALYST         7566 19-APR-87       3000
ACCOUNTING     NEW YORK      KING       PRESIDENT            17-NOV-81       5000
SALES          CHICAGO       TURNER     SALESMAN        7698 08-SEP-81       1500
RESEARCH       DALLAS        ADAMS      CLERK           7788 23-MAY-87       1100
SALES          CHICAGO       JAMES      CLERK           7698 03-DEC-81        950
RESEARCH       DALLAS        FORD       ANALYST         7566 03-DEC-81       3000
ACCOUNTING     NEW YORK      MILLER     CLERK           7782 23-JAN-82       1300

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3358798264

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |    14 |   658 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MTRLVIEW_TEST |    14 |   658 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        941  recursive calls
          0  db block gets
       1256  consistent gets
          1  physical reads
          0  redo size
       1608  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         82  sorts (memory)
          0  sorts (disk)
         14  rows processed


更新基表
SQL>  update emp set ename='KINGKING' where ename='KING';

1 row updated.
SQL> commit;
Commit complete.

查询物化视图（立即更新了）
SQL> select DNAME,LOC,ENAME,JOB,MGR,HIREDATE,SAL from mtrlview_test
  2  where ename='KINGKING';

DNAME          LOC           ENAME      JOB              MGR HIREDATE         SAL
-------------- ------------- ---------- --------- ---------- --------- ----------
ACCOUNTING     NEW YORK      KINGKING   PRESIDENT            17-NOV-81       5000