Oracle 物化檢視建立

    開始學習物化檢視，想知道物化檢視怎麼建立的，就找了點資料，進行實踐，操作如下：

以DBA許可權使用者登入；

SQL> grant  create materialized view to study;

授權成功。

SQL> grant query rewrite to study;

授權成功。

SQL> grant create any table to study;

授權成功。

SQL> grant select any table to study
  2  ;

授權成功。

以study使用者登入；

SQL> desc semp
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------

 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select empno,ename from semp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      8000 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER
      8100 geng
      8100 geng

已選擇16行。

SQL> delete from semp where empno=8100;

已刪除2行。

SQL> commit;

提交完成。

SQL> alter table semp add constraint pk_emp primary key(empno) enable novalidate
;

表已更改。

SQL> commit;

提交完成。

SQL> alter table sdept add constraint pk_dept primary key(deptno) enable novalid
ate;

表已更改。

SQL> commit;

提交完成。

SQL> create materialized view log on sdept;

實體化檢視日誌已建立。

SQL> create materialized view log on semp;

實體化檢視日誌已建立。

SQL> create materialized view vm_remp build immediate refresh fast on commit ena
ble query rewrite as select d.deptno,e.empno,d.dname,ename,e.job from semp e,sde
pt d where d.deptno=e.deptno;
create materialized view vm_remp build immediate refresh fast on commit enable q
uery rewrite as select d.deptno,e.empno,d.dname,ename,e.job from semp e,sdept d
where d.deptno=e.deptno

                                                                 *
第 1 行出現錯誤:
ORA-12052: 無法快速重新整理實體化檢視 STUDY.VM_REMP


SQL> create materialized view vm_remp build immediate refresh fast on commit ena
ble query rewrite as select d.dname,ename,e.job from semp e,sdept d where d.dept
no=e.deptno;
create materialized view vm_remp build immediate refresh fast on commit enable q
uery rewrite as select d.dname,ename,e.job from semp e,sdept d where d.deptno=e.
deptno

                                                *
第 1 行出現錯誤:
ORA-12052: 無法快速重新整理實體化檢視 STUDY.VM_REMP


SQL> create materialized view vm_remp build immediate refresh fast on commit ena
ble query rewrite as select d.rowid did,e.rowid eid, d.deptno,e.empno,d.dname,en
ame,e.job from semp e,sdept d where d.deptno=e.deptno;
create materialized view vm_remp build immediate refresh fast on commit enable q
uery rewrite as select d.rowid did,e.rowid eid, d.deptno,e.empno,d.dname,ename,e
.job from semp e,sdept d where d.deptno=e.deptno


          *
第 1 行出現錯誤:
ORA-12032: 不能使用 "STUDY"."SDEPT" 上實體化檢視日誌中的 rowid 列


SQL> create materialized view log on sdept with rowid;
create materialized view log on sdept with rowid
*
第 1 行出現錯誤:
ORA-12000: 實體化檢視日誌已經存在於表 'SDEPT' 上

SQL> drop materialized view log on sdept;

實體化檢視日誌已刪除。

SQL> drop materialized view log on semp;

實體化檢視日誌已刪除。

SQL> create materialized view log on sdept with rowid;

實體化檢視日誌已建立。

SQL> create materialized view log on semp with rowid;

實體化檢視日誌已建立。

SQL> create materialized view vm_remp build immediate refresh fast on commit ena
ble query rewrite as select d.rowid did,e.rowid eid, d.deptno,e.empno,d.dname,en
ame,e.job from semp e,sdept d where d.deptno=e.deptno;

實體化檢視已建立。

SQL>

關於物化檢視的詳細資料可以看到老楊的部落格：http://yangtingkun.itpub.net/post/468/14245