ORACLE物化檢視測試

ORACLE物化檢視測試

物化檢視是一種特殊的物理表，“物化”(Materialized)檢視是相對普通檢視而言的。普通檢視是虛擬表，應用的侷限性大，任何對檢視的查詢Oracle都實際上轉換為檢視SQL語句的查詢。這樣對整體查詢效能的提高，並沒有實質上的好處。物化檢視有ON DEMAND和ON COMMIT物化檢視ON DEMAND顧名思義，僅在該物化檢視“需要”被重新整理了，才進行重新整理(REFRESH)，即更新物化檢視，以保證和基表資料的一致性;而ON COMMIT是說，一旦基COMMIT，即事務提交，則立刻重新整理，立刻更新物化檢視，使得資料和基表一致。ORACLE預設建立的物化檢視是ON DEMAND 模式。

E:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 11月 26 10:16:39 2013

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/cat
已連線。

SQL> create materialized view mv_test as select * from dept1;    --建立ON DEMAND 物化檢視

實體化檢視已建立。

SQL> select mv.REFRESH_MODE from user_mviews mv where mv.MVIEW_NAME = 'MV_TEST';

REFRES
------
DEMAND

SQL> analyze table mv_test compute statistics;   --分析物化檢視

表已分析。

SQL> analyze table dept1 compute statistics;   --分析表

表已分析。

SQL> select t.TABLE_NAME,t.NUM_ROWS from user_tables t where t.TABLE_NAME in ('MV_TEST','DEPT1'); 　　--檢視當前基表和物化檢視的行數　

TABLE_NAME NUM_ROWS
------------------------------ ----------
DEPT1 6
MV_TEST 6

物化檢視最重要的功能和特性之一，就是其資料會隨著基表(或稱主表，master表，本例中為TESTCF)的變化而變，基表資料增了，物化檢視資料會變多;基表資料刪了，物化檢視資料也會變少。
但怎麼更新?或者說物化檢視的資料怎麼隨著基表而更新?Oracle提供了兩種方式，手工重新整理和自動重新整理。預設是手工重新整理，自動重新整理可以通過JOB等方法實現

SQL> create view v_test as select * from dept1;   --建立一個普通檢視

檢視已建立。

SQL> select * from v_test;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz

已選擇6行。

SQL> insert into dept1(deptno,dname,loc) values (70,'test','test');   --插入一條測試資料

已建立 1 行。

SQL> select * from v_test;    --普通檢視資料已變化

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
70 test test

已選擇7行。

SQL> select * from mv_test; --可以看見物化檢視資料並沒有變化

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz

已選擇6行。

建立ON COMMIT物化檢視

SQL> insert into mv_test(deptno,dname,loc) values (70,'test','test');   --物化檢視不能像普通檢視表那樣插入資料
insert into mv_test(deptno,dname,loc) values (70,'test','test')
*
第 1 行出現錯誤:
ORA-01732: 此檢視的資料操縱操作非法

SQL> create materialized view mv_test1 refresh force on commit as select * from dept1;
create materialized view mv_test1 refresh force on commit as select * from dept1
*
第 1 行出現錯誤:
ORA-12054: 無法為實體化檢視設定 ON COMMIT 重新整理屬性 --此錯誤是由於沒有建立物化檢視日誌

SQL> create materialized view log on dept1 ;
create materialized view log on dept1
*
第 1 行出現錯誤:
ORA-12014: 表 'DEPT1' 不包含主鍵約束條件

SQL> alter table dept1 add constraints pk_deptno primary key (deptno);

SQL> create materialized view log on dept1 ;

實體化檢視日誌已建立。

SQL> create materialized view mv_test1 refresh force on commit as select * from
dept1;

實體化檢視已建立。

SQL> select mv.REFRESH_MODE from user_mviews mv where mv.MVIEW_NAME = 'MV_TEST1' --檢視重新整理模式
;

REFRES
------
COMMIT

測試ON COMMIT物化檢視的更新特性

SQL> set timing on;

SQL> insert into dept1(deptno,dname,loc) values (80,'test','test');

已建立 1 行。

已用時間: 00: 00: 00.04
SQL> commit;

提交完成。

已用時間: 00: 00: 00.09
SQL> select * from mv_test1;     --可以看到資料已經更新過來了，但是對COMMIT速度會有一些影響，越大的表影響越大。

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
70 test test
80 test test

已選擇8行。

已用時間: 00: 00: 00.00

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28894640/viewspace-1061102/，如需轉載，請註明出處，否則將追究法律責任。

ORACLE物化檢視測試

相關文章