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
物化檢視是一種特殊的物理表,“物化”(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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 基於ROWID更新的物化檢視測試
- Oracle普通檢視和物化檢視的區別Oracle
- Oracle物化檢視的建立及使用(二)Oracle
- Oracle物化檢視的建立及使用(一)Oracle
- 物化檢視
- ClickHouse 效能優化?試試物化檢視優化
- ClickHouse效能優化?試試物化檢視優化
- 物化檢視(zt)
- calcite物化檢視詳解
- 【MVIEW】Oracle通過物化檢視同步表資料及簡介ViewOracle
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- 資料庫的物化檢視資料庫
- 物化檢視分割槽實驗
- 物化檢視幾個知識點
- ClickHouse 物化檢視學習總結
- StarRocks 物化檢視重新整理流程及原理
- StarRocks 物化檢視重新整理流程和原理
- 物化檢視如何快速完成資料聚合操作?
- 2.5.4. 測試Oracle net——2.5.4.2. 檢視監聽程式狀態Oracle
- Postgres使用trigger自動重新整理物化檢視
- 物化檢視快速重新整理與ORA-00001
- 使用Materialise物化檢視解耦微服務架構解耦微服務架構
- 火山引擎ByteHouse:如何最佳化ClickHouse物化檢視能力?
- hg_job配置定時重新整理物化檢視
- 物化檢視日誌無法正常清除的解決方法
- 資料泵匯出匯入物化檢視(ORA-39083)
- Oracle OCP(24):檢視Oracle
- 基於圖神經網路的動態物化檢視管理神經網路
- 用exp、imp遷移包含物化檢視日誌的資料
- 測試bsc檢視收藏品網站網站
- oracle 檢視錶空間Oracle
- 11、Oracle中的檢視Oracle
- 使用SpringBoot+PostgreSQL物化檢視實現微服務設計模式 - vinsguruSpring BootSQL微服務設計模式
- Oracle replayc測試Oracle
- 檢視oracle臨時表空間佔用率的檢視Oracle
- Oracle檢視歷史TOP SQLOracleSQL
- oracle檢視物件DDL語句Oracle物件
- 檢視ORACLE中鎖定物件Oracle物件
- ORACLE壓力測試Oracle