Oracle普通檢視和物化檢視的區別

http://blog.csdn.net/aiynmimi/article/details/54287320

介紹

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

1、物化檢視的型別：ON DEMAND、ON COMMIT
二者的區別在於 重新整理方法 的不同，ON DEMAND顧名思義， 僅在該物化檢視“需要”被重新整理了，才進行重新整理(REFRESH)，即更新物化檢視，以保證和基表資料的一致性 ；而ON COMMIT是說，一旦基表有了COMMIT ，即事務提交，則立刻重新整理，立刻更新物化檢視，使得資料和基表一致。

2、ON DEMAND物化檢視
物化檢視的建立本身是很複雜和需要最佳化引數設定的，特別是針對大型生產 系統而言。但Oracle允許以這種最簡單的，類似於普通檢視的方式來做，所以不可避免的會涉及到預設值問題。也就是說Oracle給物化檢視的重要定義引數的預設值處理是我們需要特別注意的。
物化檢視的特點：
(1) 物化檢視在某種意義上說就是一個物理表(而且不僅僅是一個物理表)，這透過其可以被user_tables查詢出來，而得到佐證；
(2) 物化檢視也是一種段(segment)，所以其有自己的物理儲存屬性；
(3) 物化檢視會佔用資料庫磁碟空間，這點從user_segment的查詢結果，可以得到佐證；

建立語句： create materialized view mv_name as select * from table_name
預設情況下，如果沒指定重新整理方法和重新整理模式， 則Oracle預設為FORCE和DEMAND。

3、物化檢視的資料怎麼隨著基表而更新？
Oracle提供了兩種方式， 手工重新整理和自動重新整理，預設為手工重新整理 。也就是說，透過我們手工的執行某個Oracle提供的系統級儲存過程或包，來保證物化檢視與基表資料一致性。這是最基本的重新整理辦法了。自動重新整理，其實也就是Oracle會建立一個job，透過這個job來呼叫相同的儲存過程或包，加以實現。

4、ON DEMAND物化檢視的特性及其和ON COMMIT物化檢視的區別
前者不重新整理(手工或自動)就不更新物化檢視，而後者不重新整理也會更新物化檢視，——只要基表發生了COMMIT。
建立定時重新整理的物化檢視：

create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1 （指定物化檢視每天重新整理一次）

上述建立的物化檢視每天重新整理，但是沒有指定重新整理時間，如果要指定重新整理時間（比如每天晚上10：00定時重新整理一次）：

create materialized view mv_name 
refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')

5、ON COMMIT物化檢視
ON COMMIT物化檢視的建立，和上面建立ON DEMAND的物化檢視區別不大。 因為ON DEMAND是預設的，所以ON COMMIT物化檢視，需要再增加個引數即可 。

需要注意的是，無法在定義時僅指定ON COMMIT，還得附帶個引數才行。
建立ON COMMIT物化檢視：

create materialized view mv_name refresh force on commit as select * from table_name

備註：實際建立過程中，基表需要有主鍵約束，否則會報錯（ORA-12014）

6、物化檢視的重新整理
重新整理（Refresh）：指當基表發生了DML操作後，物化檢視何時採用哪種方式和基表進行同步。 重新整理的模式 有兩種：ON DEMAND和ON COMMIT。（如上所述）
重新整理的方法 有四種： FAST、COMPLETE、FORCE和NEVER。
FAST重新整理採用增量重新整理，只重新整理自上次重新整理以後進行的修改。COMPLETE重新整理對整個物化檢視進行完全的重新整理。 如果選擇FORCE方式，則Oracle在重新整理時會去判斷是否可以進行快速重新整理，如果可以則採用FAST方式，否則採用COMPLETE的方式 。NEVER指物化檢視不進行任何重新整理。

對於已經建立好的物化檢視，可以 修改其重新整理方式 ，比如把物化檢視mv_name的重新整理方式修改為每天晚上10點重新整理一次：

alter materialized view mv_name 
refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')

7、物化檢視具有表一樣的特徵，所以可以像對錶一樣，我們可以為它建立索引，建立方法和對錶一樣。

8、物化檢視的刪除：
雖然物化檢視是和表一起管理的，但是在經常使用的PLSQL工具中，並不能用刪除表的方式來刪除（在表上右鍵選擇‘drop’並不能刪除物化檢視），可以使用語句來實現：

drop materialized view mv_name

==============================

其他參考

普通檢視和物化檢視的區別

普通檢視和物化檢視根本就不是一個東西，說區別都是硬拼到一起的，首先明白基本概念，普通檢視是不儲存任何資料的，他只有定義，在查詢中是轉換為對應的定義SQL去查詢，而 物化檢視是將資料轉換為一個表，實際儲存著資料，這樣查詢資料，就不用關聯一大堆表，如果表很大的話，會在臨時表空間內做大量的操作 。

普通檢視的三個特徵：
1、是 簡化設計 ，清晰編碼的東西， 他並不是提高效能的，他的存在只會降低效能 （如一個檢視7個表關聯，另一個檢視8個表，程式設計師不知道，覺得很方便，把兩個檢視關聯再做一個檢視，那就慘了），他的存在未了在設計上的方便性
2、其次，是 安全 ，在授權給其他使用者或者檢視角度，多個表關聯只允許檢視，不允許修改，單表也可以同WITH READ ONLY來控制，當然有些專案基於檢視做物件導向的開發，即在檢視上去做INSTAND OF觸發器，就我個人而言是不站同的，雖然開發上方便，但是未必是好事。
3、從不同的角度看不同的維度，檢視可以劃分維度和許可權，並使多個維度的綜合，也就是你要什麼就可以從不同的角度看，而表是一個實體的而已，一般維度較少（如：人員表和身份表關聯，從人員表可以檢視人員的維度統計，從身份看，可以看不同種類的身份有那些人或者多少人），其次另一個如系統檢視USER_TABLE、TAB、USER_OBJECTS這些檢視，不同的使用者下看到的肯定是不一樣的，看的是自己的東西。

物化檢視呢 ，用於OLAP系統中，當然部分OLTP系統的小部分功能未了提高效能會借鑑一點點，因為表關聯的開銷很大，所以在開發中很多人就像把這個代價交給定期轉存來完成，ORACLE當然也提供了這個功能，就是 將檢視（或者一個大SQL）的資訊轉換為物理資料儲存，然後提供不同的策略 ： 定時刷還是及時刷、增量刷還是全域性刷等等可以根據實際情況進行選擇，總之你差的是表，不是檢視。

關於在重新整理和索引上的區別

他們兩個沒有聯絡吧，重新整理我不清楚你是否指的是物化檢視的重新整理，因為重新整理的概念很泛，你說到這裡我就理解為物化檢視的重新整理了，上面也已經說了，這是一種策略和方法， 其實它是透過對檢視關聯表上建立相應的LOG，根據日誌資訊的SQL同步到物化檢視中的， 一般來說：定時的一般是全域性刷，及時的一般是區域性刷。

而索引這個說起來就多了，可以說索引專門是一門課程，大概點來說，索引一般有普通索引、點陣圖索引、唯一性索引（還有全文索引啥的，一般不用），其實仔細研究會發現無論是那一種索引都是 B+樹 為基礎，並起存放方式和表一樣，是以段為單位，只是內部有樹關係而已。
1、普通索引是根據B+樹找到第一個（索引時有序的），然後以當前為基準，向後順序找到不符合條件的健值為止。
2、點陣圖是在葉子節點上根據點陣圖種類對葉子節點的值進行01編碼存放（如該欄位有1、2、3三種值，就會在葉子節點上有三個點陣圖，每個點陣圖根據健值和ROWID順序存放是否為1、是否為2、是否為3，所以在RBO下統計很快，CBO下一般會認為是普通索引）。
3、也是按照B+樹找，只是找到就不再做任何操作，因為是唯一的。

因為B+查詢是一個類似表的查詢，而且獲取到ROWID後還是要回表查詢的，所以這個過程的開銷要和全表掃描計算那個結果更加快，ORACLE才會選擇是走索引還是走全表掃描，當然對於CBO和RBO選擇的方式不一樣，具體又是很多，CBO要依賴於表的統計資訊，RBO是依賴於嘗試。

關於B+樹的相關內容，可以參考下面這篇文章：
從B 樹、B+ 樹、B* 樹談到R 樹