用下面的命令创建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