熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle物化視圖:創建最簡單物化視圖

2013-11-13 15:41:28  來源: Oracle 

  物化視圖是一種特殊的物理表物化(Materialized)視圖是相對普通視圖而言的普通視圖是虛擬表應用的局限性大任何對視圖的查詢Oracle都實際上轉換為視圖SQL語句的查詢這樣對整體查詢性能的提高並沒有實質上的好處

  Oracle最早在OLAP系統中引入了物化視圖的概念但後來很多大型OLTP系統中發現類似統計的查詢是無可避免而這些查詢操作如果很頻繁對整體數據庫性能是很致命的於是Oracle開始不斷的改進物化視圖使得其也開始合適OLTP系統從Oracle i到現在功能已經相對比較完備了

  本文是Oracle物化視圖系列文章的第一篇有兩個主要目的來體驗一下創建ON DEMAND和ON COMMIT物化視圖的方法ON DEMAND和ON COMMIT物化視圖的區別在於其刷新方法的不同ON DEMAND顧名思義僅在該物化視圖需要被刷新了才進行刷新(REFRESH)即更新物化視圖以保證和基表數據的一致性;而ON COMMIT是說一旦基表有了COMMIT即事務提交則立刻刷新立刻更新物化視圖使得數據和基表一致

  第一個ON DEMAND物化視圖

  創建ON DEMAND物化視圖

  下面創建一個最簡單的物化視圖這個物化視圖的定義很類似於普通視圖的創建語句只是多了一個materialized但就是這個單詞造成了物化視圖和普通視圖(虛擬表)的天壤之別也引申出後面很多的事情呵呵

  本例中需要特別注意的是Oracle給物化視圖的重要定義參數的默認值處理在下面的例子中會有特別說明因為物化視圖的創建本身是很復雜和需要優化參數設置的特別是針對大型生產數據庫系統而言但Oracle允許以這種最簡單的類似於普通視圖的辦法來做所以不可避免的會涉及到默認值問題

  像我們這樣創建物化視圖時未作指定則Oracle按ON DEMAND模式來創建

  從下例中可以看出

  ) 物化視圖在某種意義上說就是一個物理表(而且不僅僅是一個物理表)這通過其可以被user_tables查詢出來而得到佐證;

  ) 物化視圖也是一種段(segment)所以其有自己的物理存儲屬性;

  ) 物化視圖會占用數據庫磁盤空間這點從user_segment的查詢結果可以得到佐證

  ●  創建物化視圖

  獲取數據庫rdbms版本信息  

         SQL> select * from v$version;
  BANNER
  
  Oracle Database g Enterprise Edition Release   Production
  PL/SQL Release   Production
  CORE  Production
  TNS for bit Windows: Version   Production
  NLSRTL Version  – Production

  創建物化視圖  

         SQL> create materialized view mv_testcf
   as
   select * from xiaotgtestcf;
  Materialized view created

  分析物化視圖以獲得統計信息 

         SQL> analyze table xiaotgmv_testcf compute statistics;
  Table analyzed

  查看物化視圖的行數發現和master表(TESTCF)一樣  

         SQL> select tltable_name tlnum_rows from user_tables tl where tltable_name in ( TESTCF MV_TESTCF );
  TABLE_NAME NUM_ROWS
   
  MV_TESTCF 
  TESTCF 

  查看物化視圖的存儲參數  

         SQL> col segment_name for a
  SQL> select sgsegment_name sgbytes sgblocks from user_segments sg where sgsegment_name = MV_TESTCF;
  SEGMENT_NAME BYTES BLOCKS
    
  MV_TESTCF  

  ●  查看物化視圖關鍵定義

  查看物化視圖的定義設置請關注藍色字體部分

  這表明默認情況下如果沒指定刷新方法和刷新模式則Oracle默認為FORCE和DEMAND

  其他的集中刷新方法和刷新模式以後將分別予以介紹

  SQL> select mv* from user_mviews mv where mvMVIEW_NAME = MV_TESTCF;

  (為增加查詢結果的可讀性下面進行了行列的互轉)

         OWNERXIAOTG
  MVIEW_NAMEMV_TESTCF
  CONTAINER_NAMEMV_TESTCF
  QUERY
  QUERY_LEN
  UPDATABLEN
  UPDATE_LOG
  MASTER_ROLLBACK_SEG
  MASTER_LINK
  REWRITE_ENABLEDN                                         
  REWRITE_CAPABILITYGENERAL
  REFRESH_MODEDEMAND
  REFRESH_METHODFORCE
  BUILD_MODEIMMEDIATE
  FAST_REFRESHABLEDML
  LAST_REFRESH_TYPECOMPLETE
  LAST_REFRESH_DATE :
  STALENESSFRESH
  AFTER_FAST_REFRESHFRESH
  UNKNOWN_PREBUILTN
  UNKNOWN_PLSQL_FUNCN
  UNKNOWN_EXTERNAL_TABLEN
  UNKNOWN_CONSIDER_FRESHN
  UNKNOWN_IMPORTN
  UNKNOWN_TRUSTED_FDN
  COMPILE_STATEVALID
  USE_NO_INDEXN
  STALE_SINCE
  NUM_PCT_TABLES
  NUM_FRESH_PCT_REGIONS
  NUM_STALE_PCT_REGIONS

  測試ON DEMAND物化視圖的更新特性

  物化視圖最重要的功能和特性之一就是其數據會隨著基表(或稱主表master表本例中為TESTCF)的變化而變基表數據增了物化視圖數據會變多;基表數據刪了物化視圖數據也會變少

  但怎麼更新?或者說物化視圖的數據怎麼隨著基表而更新?Oracle提供了兩種方式手工刷新和自動刷新像我們這種在物化視圖定義時未作任何指定那當然是默認的手工刷新了也就是說通過我們手工的執行某個Oracle提供的系統級存儲過程或包來保證物化視圖與基表數據一致性

  這是最基本的刷新辦法了但所謂的自動刷新其實也就是Oracle會建立一個job通過這個job來調用相同的存儲過程或包加以實現這在本系列文章的第篇會將以詳細闡述

  下面將測試INSERTUPDATE和DELETE的測試方法類似大家有興趣的話可以自己試一試

  需要注意的是下面暫不討論如何刷新ON DEMAND物化視圖這是下一篇文章的內容下面僅僅關注ON DEMAND物化視圖的特性及其和ON COMMIT物化視圖的區別即前者不刷新(手工或自動)就不更新物化視圖而後者不刷新也會更新物化視圖——只要基表發生了COMMIT

  ●  在基表插入測試數據

  基表數據插入後會發現物化視圖並不會隨之更新

  檢查基表和物化視圖是否有這一行記錄

         SQL> col id for a;
  SQL> col name for a;
  SQL> select * from xiaotgtestcf t where tid =  ;
  ID NAME
   
  SQL> select * from xiaotgmv_testcf t where tid =  ;
  ID NAME
   

  插入測試數據 

  這時發現基表有數據但物化視圖並沒有 

         SQL> insert into xiaotgtestcf
   values (  xiaotg he he);
   row inserted
  SQL> commit;
  Commit complete

   測試物化視圖數據是否更新

  從下面的實驗可以看出物化視圖數據不會更新即使等上分鐘小時或者

  關於如何使得ON DEMAND物化視圖數據被更新參加本系列的第二篇文章哈)  

         SQL> select * from xiaotgtestcf t where tid =  ;
  ID NAME
   
   xiaotg he he
  SQL> select * from xiaotgmv_testcf t where tid =  ;
  ID NAME
   
  SQL>

  第一個ON COMMIT物化視圖

  最簡單的ON COMMIT物化視圖的創建和上面創建ON DEMAND的物化視圖區別不大因為ON DEMAND是默認的所以ON COMMIT物化視圖需要再增加個參數即可

   創建ON COMMIT物化視圖

  ●  創建物化視圖

  需要注意的是無法在定義時僅指定ON COMMIT還得附帶個參數才行本例中附帶refresh force關於這個參數的意思以後將加以闡述

  創建ON COMMIT物化視圖

         SQL> create materialized view mv_testcf
   refresh force on commit
   as
   select * from xiaotgtestcf;
  Materialized view created
  SQL>

  分析物化視圖和基表  

         SQL> analyze table xiaotgmv_testcf compute statistics;
  Table analyzed
  SQL> analyze table xiaotgtestcf compute statistics;
  Table analyzed

  查看當前基表和物化視圖的行數  

         SQL> select tltable_name tlnum_rows from user_tables tl where tltable_name in ( TESTCF MV_TESTCF );
  TABLE_NAME NUM_ROWS
   
  MV_TESTCF 
  TESTCF 

  ●  查看物化視圖關鍵定義

  可以從DBA_MVIEWS中看出刷新模式為COMMIT這也是它和上面ON DEMAND物化視圖的唯一區別 

       SQL> select mv* from user_mviews mv where mvMVIEW_NAME = MV_TESTCF;

  (為增加查詢結果的可讀性下面進行了行列的互轉且只顯示前三個關鍵列的)  

         REFRESH_MODECOMMIT
  REFRESH_METHODFORCE
  BUILD_MODEIMMEDIATE

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

  ON COMMIT物化視圖會在基表一旦提交時就會立刻更新物化視圖本身而且一般僅在物化視圖數據也被更新後基表數據才會事實的提交

  這意味著這種模式可能會導致延遲基表數據的提交這點在下面的實驗中體現得很清楚

  實驗中對基表TESTCF平常的COMMIT在秒內可以完成但在有了ON COMMIT視圖MV_TESTCF居然要速度減低了很多倍ON COMMIT視圖對基表的影響可見一斑

  ●  在基表中插入數據  

  
SQL> set timing on;
  SQL> insert into xiaotgtestcf ( id name ) values (   xiaotg again he he );
   row inserted
  Executed in  seconds
  SQL> commit;
  Commit complete
  Executed in  seconds
  SQL> select * from xiaotgtestcf where id = ;
  ID NAME
   
   xiaotg again he he
  Executed in  seconds
  SQL> col id for a;
  SQL> col name for a;
  SQL> select * from xiaotgtestcf where id = ;
  ID NAME
   
   xiaotg again he he
  Executed in  seconds
  SQL> select * from xiaotgmv_testcf where id = ;
  ID NAME
    
   xiaotg again he he
  Executed in  seconds

  ●  測試基表正常情況下的COMMIT速度  

         SQL> drop materialized view mv_testcf;
  Materialized view dropped
  Executed in  seconds
  SQL>
  SQL>
  SQL> insert into xiaotgtestcf ( id name ) values (   xiaotg again he he  );
   row inserted
  Executed in  seconds
  SQL> commit;
  Commit complete
  Executed in  seconds


From:http://tw.wingwit.com/Article/program/Oracle/201311/17144.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.